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

Question

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
 

Answer 1

Pas de curseur, de boucle WHILE ou de fonction définie par l'utilisateur .

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 initiale ne précisait 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
 

Answer 2

à l'aide de XML chemin ne sera pas parfaitement concaténer comme vous vous en doutez... il va remplacer les "&" par "&" et aussi gâcher <" and "> ...peut-être un peu d'autres choses, je ne sais pas...mais vous pouvez essayer ce

Je suis tombé sur une solution de contournement pour ce... vous avez besoin de remplacer:

FOR XML PATH('')
)

avec:

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

...ou NVARCHAR(MAX) si c'est ce que vous êtes en utilisant.

pourquoi diable n'a pas d' SQL ont un concaténer fonction d'agrégation? c'est un pain PITA.

Answer 3

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

La dernière version de mon code (qui ne répond pas à la question d'origine, 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 séparateur et de remplacer tous les espaces par des virgules, c'est juste pré-pend une virgule et un espace pour chaque valeur, puis utilise STUFF supprimer les deux premiers caractères.

Le codage XML est prise en charge automatiquement en utilisant le TYPE de directive.

Answer 4

Une autre option utilisant Sql Server 2005 et plus

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

Answer 5

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