Combine rows to columns using FOR XML PATH

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.