SQL - Concaténer plusieurs lignes dans une colonne

Ça arrive de temps en en temps : j'ai besoin de remonter dans une colonne une liste de pleins d'éléments présent dans plusieurs lignes d'une sous table.

Pour être concret :
On a une table activité : dbo.activite qui contient les colonnes: actId; actNom
On a une table participant d'une activité : dbo.participant qui contient les colonnes : parId, parNom, actId

J'ai besoin de remonter sur une ligne les activités avec une colonne contenant les noms de tous les participants de chaque activité !

Ma proposition :
SELECT a.actId, a.actNom
, (SELECT p.parNom + ', ' as [text()] 
   FROM dbo.participant p 
   WHERE p.actId = a.actId 
   ORDER BY p.parId 
   FOR XML PATH('')) AS participants
FROM dbo.activite a 

Cette requête remonte les infos de la table activité et une colonne participants qui contient les noms séparés par une virgule des participants de l'activité.

Les points à noter :
La colonne participants est construite par une sous requête qui remonte en XML (FOR XML PATH('')) les noms des participants en y ajoutant un séparateur (la virgule + espace ici),
Cette sous requête, remontée dans une colonne de résultat, a pour effet de concaténer les lignes de XML en une seule valeur.
La colonne remontée est renommée AS [text()], fonction de conversion XML qui permet à SQL Server de retirer les balises XML ! (Tout est la !!) . Cela nettoie la chaîne concaténée des balises xml inutiles ici (Attention text() est sensible à la casse : il doit être écrit en minuscule, n'oubliez pas non plus les parenthèses pour que la méthode soit invoquée, le tout entre crochet ou apostrophes pour respecter la syntaxe SQL ).

A noter la clause Order By qui s'assure de l'ordre de présentation des participants.

Reste un petite erreur dans ce qui est remonté : La liste des noms se termine par un séparateur (la virgule + espace ici), il faudra filtrer cela dans l'applicatif ou complexifier la requête pour retirer les 2 derniers caractères inutiles.


Maj 05/2021 : Attention aussi au contenu remonté, s'il y a des caractères spéciaux (pour l'XML) ils seront XML encodé, il faudra donc les décoder avant de les utiliser. Avec par exemple CONVERT(NVARCHAR(MAX), CONVERT(XML, participants)dans l'exemple ci dessus.
 
 

Enjoy !