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.