Combine rows to columns using FOR XML PATH

Rajnilari2015
Posted by Rajnilari2015 under Sql Server category on | Points: 40 | Views : 485
Suppose we have a table with data as under

id	name	somecolumn
1 ABC X
1 ABC Y
1 ABC Z
2 MNO R
2 MNO S


We need the output as under

ID  Name    SomeColumn
1 ABC X,Y,Z
2 MNO R,S


The below code will help to do so

SELECT ID,Name,
STUFF((SELECT ',' + CAST(T2.SomeColumn AS VARCHAR(MAX))
FROM @T T2 WHERE T1.id = T2.id AND T1.name = T2.name
FOR XML PATH('')),1,1,'') SOMECOLUMN
FROM @T T1
GROUP BY id,Name


We are performing a self join on the matching fields ID and name and then joining them by using FOR XML PATH. Finally we are converting the result of FOR XML PATH from XML to String and stripping the leading ',' (comma) by using the STUFF function.

Comments or Responses

Login to post response