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
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 !
