Concatenated results in a select in SQL Server

My colleague wanted to have concatenated result in a query like this :

Class Participants

C1 John, Albert, Frank, Neda, Abdelkader, Ismaël

C2 Liisi, Daniel, Didier, Dimitri,

...

There are some solutions out there like with Using a CTE, doing a simple concatenation in a variable.

My solution is simpler and direct :

Do your standard select with XML Explicit

SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users FOR XML EXPLICIT

this bring a result like

<Users FirstName="John"/><Users FirstName="Albert"/><Users FirstName="Frank"/><Users FirstName="Neda"/><Users FirstName="Abdelkader"/><Users FirstName="Ismaël"/><Users FirstName="Liisi"/><Users FirstName="Daniel"/><Users FirstName="Didier"/><Users FirstName="Dimitri"/>

I specify XML Explicit so that I can replace the tag's name when I insert the query as an inner query and be sure that it's really the tag name !

SELECT ClassName,

REPLACE( REPLACE((SELECT 1 AS TAG,NULL AS PARENT,FirstName AS [Users!1!FirstName] FROM Users, classes_users WHERE Users.userid=Classes_users.Users AND classes_users.classes = classId FOR XML EXPLICIT ),'',', ') AS Users

FROM classes

And it works :

The unwanted side effect is that now you should remove the trailing , at the end.

Could be done with a substring or in the application that fetches the data.