Question Comment concaténer le texte de plusieurs lignes dans une seule chaîne de texte dans SQL Server?


Considérons une table de base de données contenant des noms, avec trois lignes:

Peter
Paul
Mary

Y at-il un moyen facile de transformer cela en une seule chaîne de Peter, Paul, Mary?


1483
2017-10-11 23:49


origine


Réponses:


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

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

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é ce qui suit T-SQL:

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 si vous pouvez concaténer les virgules au début et utiliser substring Pour ignorer le premier, vous n'avez pas 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 ('')
        ), 2, 1000) [Students]
From dbo.Students ST2

1105
2018-02-13 11:53



Cette réponse peut renvoyer des résultats inattendus lorsqu'une clause ORDER BY est présente. Pour obtenir des résultats cohérents, utilisez l'une des méthodes FOR XML PATH détaillées dans d'autres réponses.

Utilisation COALESCE:

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

Juste quelques explications (puisque cette réponse semble obtenir des vues relativement régulières):

  • Coalesce est vraiment juste une triche 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 un NUL Valeur du nom (s'il y a un NUL, la NUL fera @Names  NUL après cette rangée, et la rangée suivante recommencera comme une chaîne vide. Facilement fixé 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

Selon le comportement que vous voulez (la première option filtre juste NULs out, la deuxième option les garde dans la liste avec un message de marqueur [remplacer 'N / A' par ce qui vous convient]].


893
2017-10-12 00:18



Une méthode non encore montrée via le XML  data() commande 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('')

résultats:

"Peter, Paul, Mary, "

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

Modifier: Comme l'a adopté le commentaire de @ NReilingh, vous pouvez utiliser la méthode suivante pour supprimer la virgule de fin. 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

296
2018-04-05 21:19



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 le Pour la syntaxe JSON

c'est à dire.

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 deviendra

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

Cela fonctionnera même si vos données contiennent des caractères XML incorrects

la '"},{"_":"' est sûr, car si vos données contiennent '"},{"_":"', il sera échappé à "},{\"_\":\"

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


Et dans SQL Server 2017, Azure SQL Database

Vous pouvez utiliser le nouveau STRING_AGG fonction


214
2018-03-14 05:00



SQL Server 2017+ et SQL Azure: STRING_AGG

En commençant par la prochaine version de SQL Server, nous pouvons enfin concaténer entre les lignes sans recourir à une variable ou à une sorcellerie XML.

STRING_AGG (Transact-SQL)

Sans grouper

SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;

Avec le regroupement:

SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;

Avec groupement et sous-tri

SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;

167
2017-10-12 00:10



En MySQL il y a une fonction, GROUP_CONCAT (), ce 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

98
2018-04-05 07:08



Utilisation SE FONDRE - En savoir plus à partir d'ici

À titre d'exemple:

102

103

104

Ensuite, écrivez le code ci-dessous dans le serveur sql,

Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers 
SELECT  @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM   TableName where Number IS NOT NULL

SELECT @Numbers

La sortie serait:

102,103,104

52
2018-03-08 16:29



Oracle 11g Release 2 prend en charge la fonction LISTAGG. Documentation ici.

COLUMN employees FORMAT A50

SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM   emp
GROUP BY deptno;

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

3 rows selected.

Attention

Veillez à implémenter cette fonction s'il est possible que la chaîne résultante dépasse les 4000 caractères. Il va lancer une exception. Si c'est le cas, vous devez soit gérer l'exception soit lancer votre propre fonction qui empêche la chaîne jointe de dépasser les 4000 caractères.


42
2017-08-09 21:20



Les tableaux Postgres sont géniaux. Exemple:

Créer des données de test:

postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE                                      
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');                                                          
INSERT 0 3
test=# select * from names;
 name  
-------
 Peter
 Paul
 Mary
(3 rows)

Agrège-les dans un tableau:

test=# select array_agg(name) from names;
 array_agg     
------------------- 
 {Peter,Paul,Mary}
(1 row)

Convertissez le tableau en une chaîne délimitée par des virgules:

test=# select array_to_string(array_agg(name), ', ') from names;
 array_to_string
-------------------
 Peter, Paul, Mary
(1 row)

TERMINÉ

Depuis PostgreSQL 9.0 c'est encore plus facile.


41
2017-07-06 12:46



Dans SQL Server 2005 et versions ultérieures, utilisez la requête ci-dessous pour concaténer les lignes.

DECLARE @t table
(
    Id int,
    Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d' 

SELECT ID,
stuff(
(
    SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'') 
FROM (SELECT DISTINCT ID FROM @t ) t

27
2017-10-12 00:16



Je n'ai pas accès à un serveur SQL à la maison, donc je devine la syntaxe ici, mais c'est plus ou moins:

DECLARE @names VARCHAR(500)

SELECT @names = @names + ' ' + Name
FROM Names

23
2017-08-09 21:06