how to get multiple rows in one row in sql server

SELECT  STUFF((select ','+MD.DepartmentName

         from DepartmentMaster MD where MD.PK_DepartmentID in ( select TPD.FK_DepartmentID from ProductDepartment TPD where TPD.FK_ProductID=PM.PK_ProductID )

         order by '],['+md.DepartmentName

         for xml path('')),1,1,'')DepartmentName

FROM MstProduct PM

how to use ternary operator in sql

Description:
Here i will explain how to use ternary operator in sql server. Ternary operator works like IF condition. It check the condition, if condition is true then return result1 else return result2 as you defined.

Syntax:

SELECT (CASE [ColumnName]

        WHEN [Value]
        THEN [Result1]

        ELSE [Result2]

        END)

FROM [TableName]

Example:

SELECT (CASE Department

        WHEN 'software'
        THEN 'winod'

        ELSE 'jaydeep'

        END)

FROM Department

how to use subquery in sql server 2008 with example

Description:
Here i will explain how to create subquery in sql server with example and syntax.

What is subquery in sql?
In SQL Server, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the "WHERE" clause, the "FROM" clause, or the "SELECT" clause.

Syntax:

SELECT [ColumnName] FROM [TableName]

WHERE [ColumnName] in (SELECT [ColumnName]FROM [TableName])

Example:

SELECT ItemName FROM tblItem 

WHERE ItemName in (SELECT ItemName FROM tblOrder)

In above example, we get those ItemName whis is available in tblOrder table. We can also use subquery in "SELECT" and "FROM" clause.