How to get comma separated values from table rows in SQL Server.


Create table #Employee
(
  EmpId int,
  EmpName Varchar(100),
  Department Varchar(100)
)

Insert into #Employee(EmpId,EmpName,Department)
Select 1,'Hitesh','Csharp' Union
Select 2,'Rahul','Java' Union
Select 3,'Ravi','Java' Union
Select 4,'Priyanka','English' Union
Select 5,'Dev','Csharp'

Select Department,
STUFF((SELECT ', ' + CAST(EmpName AS VARCHAR(1000))
         FROM #Employee
         WHERE Department = t.Department
         FOR XML PATH(''), TYPE)
        .value('.','VARCHAR(MAX)'),1,2,' ') EmpName
FROM #Employee t
Group by Department

Drop table #Employee