How to get the row values as comma separated in sql server 2017?


To get the row values as comma separated in sql server 2017 there are three possible ways to achieve it by using COALESCE,STUFF,SUBSTRING and XML PATH.

Department Table
DepartmentId DepartmentName
1 IT
2 Electrical
3 Mechanical
4 Civil
5 Chemical

Solution 1: Using COALESCE

Declare @DepartmentNames Varchar(1000) #break Select @DepartmentNames=COALESCE(@DepartmentNames + ',', '')+DepartmentName FROM Department; #break Select @DepartmentNames AS DepartmentNames;
Result:
IT, Electrical, Mechanical, Civil, Chemical

Solution 2: Using STUFF And XML PATH

Select STUFF( #break ( #break Select ', ' + DepartmentName From #break ( #break Select DepartmentName From Department #break ) AS D For XML PATH('') #break ) #break ,1,1,'') DepartmentNames
Result:
IT, Electrical, Mechanical, Civil, Chemical

Solution 3: Using SUBSTRING And XML PATH

Select SUBSTRING( #break ( #break Select ',' + DepartmentName From Department #break For XML PATH('')), 2,100000 #break ) AS DepartmentNames
Result:
IT, Electrical, Mechanical, Civil, Chemical
Share On