Get column value by comma separated in sql

select distinct t.[user],
  STUFF((SELECT distinct ', ' + t1.department
         from yourtablename t1
         where t.[user] = t1.[user]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,'') department
from yourtablename t;

how to use subquery in sql

Syntax:

SELECT [ColumnName]

FROM [TableName]

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

Example:

SELECT ItemName

FROM tblItem

WHERE ItemName IN (SELECT ItemName FROM tblOrder)

how to use ternary operator in sql query

Syntax:

SELECT (CASE [ColumnName]

               WHEN [Value] THEN

                     [Result1]

               ELSE

                     [Result2]

         END)

FROM

      [TableName]


Example:

SELECT (CASE Department

               WHEN 'software' THEN

                     'winod'

               ELSE

                     'shailesh'

         END)

FROM

      Order