Comment utiliser GROUP BY pour concaténer des chaînes dans SQL Server?

Comment puis-je obtenir:

id       Name       Value
1          A          4
1          B          8
2          C          9

À

id          Column
1          A:4, B:8
2          C:9
299
demandé sur Adrian Carneiro 2008-11-07 22:08:00

16 réponses

Aucun curseur, boucle WHILE ou fonction définie par L'Utilisateur nécessaire .

Juste besoin d'être créatif avec FOR XML et PATH.

[Note: Cette solution ne fonctionne que sur SQL 2005 et versions ultérieures. La question originale ne spécifiait pas la version utilisée.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable
476
répondu Kevin Fairchild 2013-07-29 19:15:38

L'utilisation du chemin XML ne concaténera pas parfaitement comme vous pouvez vous y attendre... il remplacera " & " par " & " et va également jouer avec <" and "> ...peut-être quelques autres choses, pas sûr...mais vous pouvez essayer ceci

Je suis tombé sur une solution de contournement pour cela... vous devez remplacer:

FOR XML PATH('')
)

Avec:

FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')

...ou NVARCHAR(MAX) si c'est ce que vous utilisez.

Pourquoi diable SQL n'a-t-il pas une fonction d'agrégat concaténé? c'est un pain PITA.

45
répondu Allen 2014-04-24 04:09:34

J'ai rencontré quelques problèmes lorsque j'ai essayé de convertir la suggestion de Kevin Fairchild pour travailler avec des chaînes contenant des espaces et des caractères XML Spéciaux(&, <, >) qui ont été codés.

La version finale de mon code (qui ne répond pas à la question initiale mais peut être utile à quelqu'un) ressemble à ceci:

CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT  [ID],
  STUFF((
    SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
    FROM #YourTable WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE 
     /* Use .value to uncomment XML entities e.g. &gt; &lt; etc*/
    ).value('.','VARCHAR(MAX)') 
  ,1,2,'') as NameValues
FROM    #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

Plutôt que d'utiliser un espace comme délimiteur et de remplacer tous les espaces par des virgules, il pré-pend juste une virgule et un espace à chaque valeur puis utilise STUFF pour supprimer les deux premiers caractères.

Le codage XML est pris en charge automatiquement en utilisant la directive Type.

33
répondu Jonathan Sayce 2018-01-23 15:18:19

Si C'est SQL Server 2017 ou SQL Server vNext, SQL Azure vous pouvez utiliser string_agg comme ci-dessous:

select id, string_agg(concat(name, ':', [value]), ', ')
    from #YourTable 
    group by id
26
répondu Kannan Kandasamy 2017-04-27 17:32:28

Une autre option utilisant Sql Server 2005 et supérieur

---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439       ,'CKT','Approved'
insert @t select 1125439       ,'RENO','Approved'
insert @t select 1134691       ,'CKT','Approved'
insert @t select 1134691       ,'RENO','Approved'
insert @t select 1134691       ,'pn','Approved'

---- actual query
;with cte(outputid,combined,rn)
as
(
  select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
  from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid
21
répondu cyberkiwi 2010-06-10 10:31:45

Installez les agrégats SQLCLR à partir de http://groupconcat.codeplex.com

Ensuite, vous pouvez écrire du code comme ceci pour obtenir le résultat que vous avez demandé:

CREATE TABLE foo
(
 id INT,
 name CHAR(1),
 Value CHAR(1)
);

INSERT  INTO dbo.foo
    (id, name, Value)
VALUES  (1, 'A', '4'),
        (1, 'B', '8'),
        (2, 'C', '9');

SELECT  id,
    dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM    dbo.foo
GROUP BY id;
13
répondu Orlando Colamatteo 2016-03-19 03:40:12

SQL Server 2005 et versions ultérieures vous permettent de créer vos propres Fonctions d'agrégation personnalisées , y compris pour des choses comme la concaténation-voir l'exemple au bas de l'article lié.

12
répondu Joel Coehoorn 2008-11-20 04:11:23

, Huit ans plus tard... Microsoft SQL Server vNext Database Engine a finalement amélioré Transact-SQL pour soutenir directement la concaténation de chaînes groupées. La version 1.0 de Community Technical Preview a ajouté la fonction STRING_AGG et CTP 1.1 a ajouté la clause WITHIN GROUP pour la fonction STRING_AGG.

Référence: https://msdn.microsoft.com/en-us/library/mt775028.aspx

9
répondu Shem Sargent 2017-02-10 21:03:57

Juste pour ajouter à ce que Cade a dit, c'est généralement une chose d'affichage frontal et devrait donc être traitée là. Je sais que parfois il est plus facile d'écrire quelque chose à 100% en SQL pour des choses comme l'exportation de fichiers ou d'autres solutions "SQL uniquement", mais la plupart du temps cette concaténation doit être gérée dans votre couche d'affichage.

7
répondu Tom H 2013-03-06 14:42:04

Dans Oracle, vous pouvez utiliser la fonction d'agrégation LISTAGG. Un exemple serait:

name   type
------------
name1  type1
name2  type2
name2  type3

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name

Entraînerait:

name   type
------------
name1  type1
name2  type2; type3
7
répondu Michal B. 2017-02-15 10:55:52

Ce genre de question est posé ici très souvent, et la solution va dépendre beaucoup des exigences sous-jacentes:

Https://stackoverflow.com/search?q=sql+pivot

Et

Https://stackoverflow.com/search?q=sql+concaténer

En règle générale, il n'y a pas de moyen SQL uniquement sans sql dynamique, une fonction définie par l'utilisateur ou un curseur.

6
répondu Cade Roux 2017-05-23 11:55:01

Ceci est juste un ajout au post de Kevin Fairchild (très intelligent en passant). Je l'aurais ajouté comme commentaire, mais je n'ai pas encore assez de points:)

J'utilisais cette idée pour une vue sur laquelle je travaillais, mais les éléments que je concatissais contenaient des espaces. J'ai donc légèrement modifié le code pour ne pas utiliser d'espaces comme délimiteurs.

Encore une fois merci pour la solution de contournement cool Kevin!

CREATE TABLE #YourTable ( [ID] INT, [Name] CHAR(1), [Value] INT ) 

INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'A', 4) 
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'B', 8) 
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (2, 'C', 9) 

SELECT [ID], 
       REPLACE(REPLACE(REPLACE(
                          (SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A 
                           FROM   #YourTable 
                           WHERE  ( ID = Results.ID ) 
                           FOR XML PATH (''))
                        , '</A><A>', ', ')
                ,'<A>','')
        ,'</A>','') AS NameValues 
FROM   #YourTable Results 
GROUP  BY ID 

DROP TABLE #YourTable 
6
répondu Phillip 2011-05-09 16:12:18

N'ont pas besoin d'un curseur... une boucle while est suffisante.

------------------------------
-- Setup
------------------------------

DECLARE @Source TABLE
(
  id int,
  Name varchar(30),
  Value int
)

DECLARE @Target TABLE
(
  id int,
  Result varchar(max) 
)


INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9


------------------------------
-- Technique
------------------------------

INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id

DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)

WHILE @id is not null
BEGIN
  SET @Result = null

  SELECT @Result =
    CASE
      WHEN @Result is null
      THEN ''
      ELSE @Result + ', '
    END + s.Name + ':' + convert(varchar(30),s.Value)
  FROM @Source s
  WHERE id = @id

  UPDATE @Target
  SET Result = @Result
  WHERE id = @id

  SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END

SELECT *
FROM @Target
5
répondu Amy B 2008-11-07 19:29:26

Soyons très simples:

SELECT stuff(
    (
    select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb 
    FOR XML PATH('')
    )
, 1, 2, '')

Remplacer cette ligne:

select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb

Avec votre requête.

4
répondu Marquinho Peli 2015-09-22 11:56:39

N'a pas vu de réponses croisées, pas non plus besoin d'extraction xml. Voici une version légèrement différente de ce que Kevin Fairchild a écrit. C'est plus rapide et plus facile à utiliser dans des requêtes plus complexes:

   select T.ID
,MAX(X.cl) NameValues
 from #YourTable T
 CROSS APPLY 
 (select STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
    FROM #YourTable 
    WHERE (ID = T.ID) 
    FOR XML PATH(''))
  ,1,2,'')  [cl]) X
  GROUP BY T.ID
3
répondu Mordechai 2017-03-15 10:29:16

Vous pouvez améliorer les performances de la manière suivante si group by contient principalement un élément:

SELECT 
  [ID],

CASE WHEN MAX( [Name]) = MIN( [Name]) THEN 
MAX( [Name]) NameValues
ELSE

  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues

END

FROM #YourTable Results
GROUP BY ID
2
répondu Eduard 2015-06-23 12:58:33