Comment fonctionnent Stuff et ‘For Xml Path’ dans SQL Server ?
Voici comment cela fonctionne :
1. Obtenir une chaîne d’éléments XML avec FOR XML
Ajouter FOR XML PATH à la fin d’une requête vous permet de produire les résultats de la requête sous forme d’éléments XML, avec le nom de l’élément contenu dans l’argument PATH. Par exemple, si nous exécutons l’instruction suivante :
SELECT ',' + name
FROM temp1
FOR XML PATH ('')
En passant une chaîne vide (FOR XML PATH(‘’)), nous obtenons plutôt ceci :
,aaa,bbb,ccc,ddd,eee
2. Supprimer la virgule initiale avec STUFF
L’instruction STUFF “insère” littéralement une chaîne dans une autre, en remplaçant des caractères dans la première chaîne. Cependant, nous l’utilisons ici simplement pour supprimer le premier caractère de la liste de valeurs résultante.
SELECT abc = STUFF((
SELECT ',' + NAME
FROM temp1
FOR XML PATH('')
), 1, 1, '')
FROM temp1
Les paramètres de STUFF sont :
La chaîne dans laquelle “insérer” (dans notre cas, la liste complète des noms avec une virgule initiale)
La position de début pour la suppression et l’insertion de caractères (1, nous insérons dans une chaîne vide)
Le nombre de caractères à supprimer (1, soit la virgule initiale)
Le caractère de remplacement (un caractère vide, ‘’, dans ce cas)
Nous obtenons donc :
aaa,bbb,ccc,ddd,eee
3. Joindre sur l’id pour obtenir la liste complète
Ensuite, nous joignons simplement cela sur la liste des id dans la table temporaire, pour obtenir une liste d’IDs avec les noms :
SELECT ID, abc = STUFF(
(SELECT ',' + name
FROM temp1 t1
WHERE t1.id = t2.id
FOR XML PATH (''))
, 1, 1, '') from temp1 t2
group by id;
Et nous avons notre résultat :
Id
Name
1
aaa,bbb,ccc,ddd,eee