2115 votes

Concaténer plusieurs lignes en une seule chaîne de texte ?

Considérons 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 ?

27 votes

Pour des réponses spécifiques à SQL Server, essayez cette question .

20 votes

Pour MySQL, consultez Groupe_Concat de cette réponse

28 votes

J'aimerais que la prochaine version de SQL Server offre une nouvelle fonction permettant de résoudre la concaténation de chaînes de caractères sur plusieurs rangs de manière élégante, sans la stupidité de FOR XML PATH.

1550voto

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

S'il existe une table appelée STUDENTS

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

Le résultat auquel je m'attendais é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 ('')
            ) [Students]
        From dbo.Students ST2
    ) [Main]

Vous pouvez faire la même chose de manière plus compacte en concaténant les virgules au début et en utilisant substring pour sauter la première afin de ne pas avoir à effectuer 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 ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

1 votes

Je reçois une erreur "syntaxe incorrecte près du mot clé 'For'" en utilisant MS SQL Server 2008 R2.

16 votes

Excellente solution. Ce qui suit peut être utile si vous devez gérer des caractères spéciaux comme ceux du HTML : Rob Farley : Gestion des caractères spéciaux avec FOR XML PATH('') .

0 votes

Que faire s'il n'y a pas d'identification du sujet ?

1070voto

Chris Shaffer Points 18066

Utilisez COALESCE :

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

Juste quelques explications (puisque cette réponse semble être vue assez régulièrement) :

  • Coalesce n'est en fait qu'un tricheur utile qui accomplit deux choses :

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

2) Pas besoin d'enlever un séparateur supplémentaire à la fin.

  • La solution ci-dessus donnera des résultats incorrects si une ligne a une valeur NULL Valeur du nom (s'il existe un NULL le NULL fera @Names NULL après cette ligne, et la ligne suivante recommencera comme une chaîne vide. Facile à corriger avec l'une des deux solutions suivantes :

    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 que vous souhaitez (la première option se contente de filtrer les *NULL*, la deuxième option les conserve dans la liste avec un message de marquage [remplacez 'N/A' par ce qui vous convient]).

76 votes

Pour être clair, coalesce n'a rien à voir avec la création de la liste, il s'assure simplement que les valeurs NULL ne sont pas incluses.

19 votes

@Graeme Perrow Il n'exclut pas les valeurs NULL (un WHERE est nécessaire à cet effet - ceci sera perdre les résultats si l'une des valeurs d'entrée est NULL), et il est nécessaire dans cette approche car : NULL + non-NULL -> NULL et non-NULL + NULL -> NULL ; de plus, @Name est NULL par défaut et, en fait, cette propriété est utilisée ici comme une sentinelle implicite pour déterminer si un ', ' doit être ajouté ou non.

1 votes

Il y a deux façons de corriger cela pour ignorer les NULL : Soit SELECT @Names = @Names + ', ' + Name FROM People WHERE Name IS NOT NULL ou bien SELECT @Names = COALESCE(@Names + ', ' + Name, @Names) FROM People .

380voto

jens frandsen Points 709

Une méthode qui n'a pas encore été présentée via la commande XML data() dans MS SQL Server est :

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

select FName + ', ' as 'data()' 
from NameList 
for xml path('')

retours : "Peter, Paul, Mary, ".

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

16 votes

Holy s**t thats amazing ! Lorsqu'il est exécuté seul, comme dans votre exemple, le résultat est formaté comme un lien hypertexte qui, lorsqu'on clique dessus (dans SSMS), ouvre une nouvelle fenêtre contenant les données, mais lorsqu'il est utilisé dans le cadre d'une requête plus large, il apparaît simplement comme une chaîne. S'agit-il d'une chaîne de caractères ou d'un fichier xml que je dois traiter différemment dans l'application qui utilisera ces données ?

10 votes

Cette approche permet également de mettre en évidence les caractères XML tels que < et >. Ainsi, en sélectionnant '<b>' + FName + '</b>', on obtient "<b>John</b><b>Paul...".

8 votes

Une solution astucieuse. J'ai remarqué que même si je n'ajoute pas l'option + ', ' il ajoute toujours un espace unique entre chaque élément concaténé.

327voto

Steven Chong Points 320

Sur SQL Server 2005 ...

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

3 votes

Bonne utilisation de la fonction STUFF pour supprimer les deux premiers caractères.

3 votes

Je préfère cette solution, car je peux facilement l'utiliser dans une liste de sélection en ajoutant 'as <label>'. Je ne suis pas sûr de savoir comment faire cela avec la solution de @Ritesh.

14 votes

C'est mieux que la réponse acceptée parce que cette option gère également le désencadrement des caractères réservés au XML tels que < , > , & etc. qui FOR XML PATH('') s'échappera automatiquement.

140voto

Darryl Hein Points 33819

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

0 votes

Fonctionne en principe. Deux choses à prendre en compte : 1) si votre colonne n'est pas un CHAR vous devez le couler, par exemple via GROUP_CONCAT( CAST(id AS CHAR(8)) ORDER BY id ASC SEPARATOR ',') 2) si vous avez beaucoup de valeurs à venir, vous devez augmenter le taux de group_concat_max_len comme indiqué dans stackoverflow.com/a/1278210/1498405

Prograide.com

Prograide est une communauté de développeurs qui cherche à élargir la connaissance de la programmation au-delà de l'anglais.
Pour cela nous avons les plus grands doutes résolus en français et vous pouvez aussi poser vos propres questions ou résoudre celles des autres.

Powered by:

X