Comment concaténer du texte à partir de plusieurs lignes en une seule chaîne de texte dans SQL server?

Envisager une base de données de la table contenant les noms, avec trois lignes:

Peter
Paul
Mary

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

1534
demandé sur Steve Chambers 2008-10-12 03:49:59

30 réponses

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

j'ai eu un problème similaire lorsque j'ai essayé de joindre deux tables avec une ou plusieurs relations. Dans SQL 2005 j'ai trouvé que XML PATH méthode 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

résultat auquel je m'attendais:

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

j'ai utilisé le suivant 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 d'une manière plus compacte si vous pouvez concatter les virgules au début et utiliser substring pour sauter la première donc 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
1145
répondu StefanJCollier 2018-08-02 11:20:52

Cette réponse peut retourner des résultats inattendus lors de l'une clause ORDER BY est présent. Pour obtenir des résultats cohérents, utilisez l'une des méthodes FOR XML PATH décrites dans les autres réponses.

Utiliser COALESCE :

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

Juste quelques explications (depuis cette réponse semble être relativement régulière des points de vue):

  • Fusionner est vraiment juste un utile cheat que 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 rangée a un NULL valeur du nom (s'il y a un NULL , le NULL fera @Names NULL après cette ligne, et la ligne suivante recommencera à nouveau comme une chaîne vide. Facile à fixer 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 ne filtre que NULL s out, la deuxième option les garde dans la liste avec un message de marqueur [remplacer 'N/a' par ce qui est approprié pour vous]).

908
répondu Chris Shaffer 2018-04-30 09:26:56

une méthode qui n'est pas encore montrée via la commande XML data() dans le serveur MS SQL est:

table D'hypothèse 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.

Edit: tel qu'adopté dans le commentaire de @NReilingh, vous pouvez utiliser la méthode suivante pour supprimer la virgule. Supposer 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
309
répondu jens frandsen 2016-04-25 15:28:47

In SQL Server 2005

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

in 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 invalides

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

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

227
répondu Steven Chong 2018-07-23 16:55:05

SQL Server 2017+ and SQL Azure: STRING_AGG

à partir de la prochaine version de SQL Server, nous pouvons enfin concaténer à travers les lignes sans avoir à recourir à une variable ou à la sorcellerie XML.

STRING_AGG (Transact-SQL)

sans regroupement

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

avec groupement:

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

avec groupement et sous-triage

"
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department 
GROUP BY GroupName;
194
répondu Mathieu Renda 2018-07-24 05:36:45

dans MySQL il y a une fonction, GROUP_CONCAT () , qui vous permet de concaténer les valeurs à partir 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
102
répondu Darryl Hein 2012-02-26 18:37:58

Utiliser FUSIONNENT - en Savoir plus d'ici

pour un exemple:

102

103

104

puis écrivez le code ci-dessous dans sql server,

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

sortie serait:

102,103,104
53
répondu pedram 2017-09-22 23:27:27

Oracle 11g Release 2 supporte 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.

avertissement

soyez prudent en mettant en œuvre cette fonction s'il y a une possibilité que la chaîne résultante dépasse 4000 caractères. Ça fera une exception. Si c'est le cas, alors vous devez gérer l'exception ou lancer votre propre fonction qui empêche la chaîne jointe de dépasser 4000 caractères.

44
répondu Alex 2013-03-14 14:30:15

Postgres les tableaux sont géniaux. Exemple:

créer quelques données d'essai:

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)

les regrouper dans un tableau:

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

convertissez le tableau en chaîne délimitée par une virgule:

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

fait

depuis PostgreSQL 9.0 il est encore plus facile .

43
répondu hgmnz 2017-05-23 12:18:33

dans SQL Server 2005 et plus tard, 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
28
répondu Yogesh Bhadauirya 2014-10-20 08:49:30

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
24
répondu Dana 2008-10-12 00:16:20

une solution CTE récursive a été proposée, mais aucun code n'a été fourni. Le code ci-dessous est un exemple de CTE récursif -- notez que bien que les résultats correspondent à la question, les données ne correspondent pas tout à fait à la description donnée, car je suppose que vous voulez vraiment faire cela sur des groupes de lignes, pas toutes les lignes dans le tableau. Le modifier pour correspondre à toutes les lignes dans le tableau est laissé comme un exercice pour le lecteur.

;with basetable as 
(   SELECT id, CAST(name as varchar(max))name, 
        ROW_NUMBER() OVER(Partition By id     order by seq) rw, 
        COUNT(*) OVER (Partition By id) recs 
FROM (VALUES (1, 'Johnny', 1), (1,'M', 2), 
                  (2,'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6),
        (3, 'Ted', 1), (3,'Theodore', 2), (3,'Logan', 3),
                  (4, 'Peter', 1), (4,'Paul', 2), (4,'Mary', 3)

           )g(id, name, seq)
),
rCTE as (
    SELECT recs, id, name, rw from basetable where rw=1
    UNION ALL
    SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw+1
    FROM basetable b
         inner join rCTE r
    on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4
22
répondu jmoreno 2012-08-09 21:06:05

à partir de PostgreSQL 9.0 c'est assez simple:

select string_agg(name, ',') 
from names;

dans les versions antérieures à 9.0 array_agg() peut être utilisé comme indiqué par hgmnz

22
répondu a_horse_with_no_name 2014-10-20 10:42:42

vous avez besoin de créer une variable qui tiendra votre résultat final et sélectionner en elle, comme ainsi.

Solution La Plus Facile

DECLARE @char VARCHAR(MAX);

SELECT @char = COALESCE(@char + ', ' + [column], [column]) 
FROM [table];

PRINT @char;
22
répondu Tigerjz32 2016-11-15 21:07:57

dans SQL Server vNext ce sera intégré avec la fonction STRING_AGG, en savoir plus ici: https://msdn.microsoft.com/en-us/library/mt790580.aspx

20
répondu Henrik Fransas 2017-01-10 15:42:49

L'utilisation de XML m'a aidé à obtenir des lignes séparées par des virgules. Pour la virgule supplémentaire, nous pouvons utiliser la fonction remplacer de SQL Server. Au lieu d'ajouter une virgule, l'utilisation de l'AS 'data()' concaténera les lignes avec des espaces, qui plus tard peuvent être remplacés par des virgules comme la syntaxe écrite ci-dessous.

REPLACE(
        (select FName AS 'data()'  from NameList  for xml path(''))
         , ' ', ', ') 
15
répondu Diwakar 2012-02-26 18:42:55

une solution prête à l'emploi, sans virgule supplémentaire:

select substring(
        (select ', '+Name AS 'data()' from Names for xml path(''))
       ,3, 255) as "MyList"

une liste vide résultera en valeur nulle. Habituellement, vous insérerez la liste dans une colonne de tableau ou une variable de programme: ajustez la longueur max 255 à votre besoin.

(Diwakar et Jens Frandsen donné de bonnes réponses, mais ont besoin d'amélioration.)

14
répondu Daniel Reis 2012-02-26 20:03:20
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)

cela place la virgule errante au début.

cependant, si vous avez besoin d'autres colonnes, ou pour CSV une table enfant, vous devez envelopper cela dans un champ défini par l'utilisateur scalaire (UDF).

vous pouvez utiliser XML path comme sous-requête corrélée dans la clause SELECT aussi (mais je devrais attendre jusqu'à ce que je retourne au travail parce que Google ne fait pas les choses de travail à la maison : -)

8
répondu gbn 2012-02-26 18:39:58

avec les autres réponses, la personne qui lit la réponse doit être au courant d'une table de domaine spécifique comme véhicule ou étudiant. La table doit être créée et remplie de données pour tester une solution.

ci-dessous est un exemple qui utilise SQL Server" Information_Schema.Les colonnes" de la table. En utilisant cette solution, il n'est pas nécessaire de créer des tableaux ou d'ajouter des données. Cet exemple crée une liste de noms de colonne séparés par des virgules pour toutes les tables de la base de données.

SELECT
    Table_Name
    ,STUFF((
        SELECT ',' + Column_Name
        FROM INFORMATION_SCHEMA.Columns Columns
        WHERE Tables.Table_Name = Columns.Table_Name
        ORDER BY Column_Name
        FOR XML PATH ('')), 1, 1, ''
    )Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME 
8
répondu Mike Barlow - BarDev 2016-05-04 19:31:22
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')

voici un échantillon:

DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
7
répondu Max Szczurek 2018-02-28 16:04:05

pour Oracle DBs, voir la question: comment des lignes multiples peuvent-elles être concaténées en une seule dans Oracle sans créer une procédure stockée?

la meilleure réponse semble être par @Emmanuel, en utilisant la fonction LISTAGG() intégrée, disponible dans Oracle 11g version 2 et plus tard.

SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

comme @user762952 l'a souligné, et selon la documentation D'Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php , la fonction WM_CONCAT() est aussi une option. Il semble stable, mais Oracle recommande explicitement de ne pas l'utiliser pour N'importe quelle application SQL, donc utilisez à vos propres risques.

en dehors de cela, vous devrez écrire votre propre fonction; le document Oracle ci-dessus a un guide sur la façon de faire cela.

6
répondu ZeroK 2017-05-23 11:47:36

pour éviter les valeurs nulles, vous pouvez utiliser CONCAT ()

DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name) 
FROM Names
select @names
5
répondu Rapunzo 2015-02-12 12:01:27

cette réponse nécessitera un certain privilège dans le serveur pour fonctionner.

assemblages sont une bonne option pour vous. Il y a beaucoup de sites qui expliquent comment le créer. Celui que je pense très bien expliqué est ce un

Si vous voulez, j'ai déjà créé l'assemblée, et il est possible de télécharger la DLL ici .

une fois que vous avez téléchargé il, vous aurez besoin d'exécuter le script suivant dans votre serveur SQL:

CREATE Assembly concat_assembly 
   AUTHORIZATION dbo 
   FROM '<PATH TO Concat.dll IN SERVER>' 
   WITH PERMISSION_SET = SAFE; 
GO 

CREATE AGGREGATE dbo.concat ( 

    @Value NVARCHAR(MAX) 
  , @Delimiter NVARCHAR(4000) 

) RETURNS NVARCHAR(MAX) 
EXTERNAL Name concat_assembly.[Concat.Concat]; 
GO  

sp_configure 'clr enabled', 1;
RECONFIGURE

Observe que le chemin d'assemblage peut être accessible au serveur. Puisque vous avez réussi toutes les étapes, vous pouvez utiliser la fonction comme:

SELECT dbo.Concat(field1, ',')
FROM Table1

Espère que cela aide!!!

5
répondu Nizam 2015-05-08 01:39:06

MySQL exemple complet:

nous avons des utilisateurs qui peuvent avoir beaucoup de données et nous voulons avoir une sortie, où nous pouvons voir toutes les données des utilisateurs dans une liste:

résultat:

___________________________
| id   |  rowList         |
|-------------------------|
| 0    | 6, 9             |
| 1    | 1,2,3,4,5,7,8,1  |
|_________________________|

Tableau De Configuration:

CREATE TABLE `Data` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;


INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);


CREATE TABLE `User` (
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `User` (`id`) VALUES
(0),
(1);

Requête:

SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
5
répondu user1767754 2015-07-22 07:51:12

j'ai vraiment aimé l'élégance de Dana la réponse de . Voulais juste le rendre complet.

DECLARE @names VARCHAR(MAX)
SET @names = ''

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

-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
5
répondu Oleg Sakharov 2017-05-23 11:55:19

j'utilise habituellement select comme ceci pour concaténer des chaînes dans le serveur SQL:

with lines as 
( 
  select 
    row_number() over(order by id) id, -- id is a line id
    line -- line of text.
  from
    source -- line source
), 
result_lines as 
( 
  select 
    id, 
    cast(line as nvarchar(max)) line 
  from 
    lines 
  where 
    id = 1 
  union all 
  select 
    l.id, 
    cast(r.line + N', ' + l.line as nvarchar(max))
  from 
    lines l 
    inner join 
    result_lines r 
    on 
      l.id = r.id + 1 
) 
select top 1 
  line
from
  result_lines
order by
  id desc
3
répondu Vladimir Nesterovsky 2011-07-06 07:06:01

Cela peut être utile aussi

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

retourne

Peter,Paul,Mary
3
répondu endo64 2013-10-25 08:14:23

si vous voulez traiter avec nulls vous pouvez le faire en ajoutant une clause où ou ajouter une autre COALESCE autour de la première.

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
2
répondu Pramod 2011-07-27 20:05:13

dans Oracle, c'est wm_concat . Je crois que cette fonction est disponible dans la version 10G et plus.

2
répondu user762952 2012-02-26 18:44:21

--SQL Server 2005+

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)      --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT 
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT 
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;
2
répondu Graeme 2016-06-01 20:42:40