Comment concaténer du texte provenant de plusieurs lignes en une seule chaîne de texte dans SQL Server

Question

Considérez une table de base de données contenant des noms, avec trois rangées :

Peter
Paul
Mary

Existe-t-il un moyen simple de transformer cela en une seule chaîne de caractères Peter, Paul, Mary ?

Answer 1

Si vous êtes sur SQL Server 2017 ou Azure, voir la réponse de Mathieu Renda.

J'ai rencontré un problème similaire lorsque j'essayais de joindre deux tables avec des relations de un à plusieurs. En SQL 2005, j'ai trouvé que la méthode XML PATH peut gérer très facilement la concaténation des lignes.

S'il y a une table appelée STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Le résultat attendu était:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

J'ai utilisé le T-SQL suivant:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

Vous pouvez faire la même chose de manière plus compacte si vous pouvez concaténer les virgules au début et utiliser substring pour sauter la première afin de ne pas avoir besoin de faire une sous-requête:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

Answer 2

Cette réponse peut retourner des résultats inattendus. Pour des résultats cohérents, utilisez l'une des méthodes FOR XML PATH détaillées dans d'autres réponses.

Utilisez COALESCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

Juste une explication (puisque cette réponse semble être régulièrement consultée) :

• Coalesce est vraiment juste une astuce utile qui accomplit deux choses :

1) Pas besoin d'initialiser @Names avec une valeur de chaîne vide.

2) Pas besoin de retirer un séparateur supplémentaire à la fin.

• La solution ci-dessus donnera des résultats incorrects si une ligne a une valeur de nom NULL (si un NULL est rencontré, le NULL rendra la variable @Names NULL après cette ligne, et la ligne suivante recommencera avec une chaîne vide à nouveau. Facilement corrigé avec l'une des deux solutions :

  DECLARE @Names VARCHAR(8000) SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People WHERE Name IS NOT NULL

ou :

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

En fonction du comportement souhaité (la première option filtre simplement les NULL, la deuxième option les conserve dans la liste avec un message de marque [remplacez 'N/A' par ce qui est approprié pour vous]).

Answer 3

Une méthode non encore montrée via la commande XML data() dans SQL Server est :

Supposons une table appelée NameList avec une colonne appelée FName,

SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')

retourne :

"Peter, Paul, Mary, "

Seule la virgule supplémentaire doit être traitée.

Tel qu'adopté du commentaire de @NReilingh, vous pouvez utiliser la méthode suivante pour supprimer la virgule finale. En supposant les mêmes noms de table et de colonne :

STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands

Answer 4

Dans SQL Server 2005

SELECT Stuff(
  (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
  .value('text()[1]','nvarchar(max)'),1,2,N'')

---

Dans SQL Server 2016

vous pouvez utiliser la syntaxe FOR JSON

par exemple.

SELECT per.ID,
Emails = JSON_VALUE(
   REPLACE(
     (SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
    ,'"},{"_":"',', '),'$[0]._'
) 
FROM Person per

Et le résultat sera

Id  Emails
1   abc@gmail.com
2   NULL
3   def@gmail.com, xyz@gmail.com

Ceci fonctionnera même si vos données contiennent des caractères XML invalides

le '"},{"_":"' est sécurisé car si vos données contiennent '"},{"_":"', cela sera échappé en "},{\"_\":\"

Vous pouvez remplacer ', ' par n'importe quel séparateur de chaîne

---

Et dans SQL Server 2017, Azure SQL Database

Vous pouvez utiliser la nouvelle fonction STRING_AGG

Answer 5

Dans MySQL, il existe une fonction, GROUP_CONCAT(), qui vous permet de concaténer les valeurs de plusieurs lignes. Exemple :

SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people 
FROM users 
WHERE id IN (1,2,3) 
GROUP BY a