Wie verwendet man GROUP BY zum Verketten von Zeichenfolgen in SQL Server?

Question

Wie bekomme ich:

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

zu

id          Column
1          A:4, B:8
2          C:9

Answer 1

Kein CURSOR, keine WHILE-Schleife, keine benutzerdefinierte Funktion erforderlich .

Man muss nur mit FOR XML und PATH kreativ sein.

[Hinweis: Diese Lösung funktioniert nur mit SQL 2005 und höher. In der ursprünglichen Frage wurde die verwendete Version nicht angegeben].

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

Wenn es sich um SQL Server 2017 oder SQL Server Vnext, SQL Azure handelt, können Sie verwenden string_agg wie unten:

select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable 
group by id

Answer 3

Die Verwendung des XML-Pfads führt nicht zu einer perfekten Verkettung, wie Sie vielleicht erwarten... er ersetzt "&" durch "&" und bringt auch die <" and "> ...vielleicht noch ein paar andere Dinge, ich bin mir nicht sicher...aber Sie können folgendes versuchen

Ich bin auf eine Lösung für dieses Problem gestoßen... Sie müssen es ersetzen:

FOR XML PATH('')
)

mit:

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

...oder NVARCHAR(MAX) wenn Sie das verwenden.

warum zum Teufel nicht SQL Haben Sie eine Funktion zum Verketten von Aggregaten? Das ist eine PITA.

Answer 4

Ich bin auf ein paar Probleme gestoßen, als ich versucht habe, Kevin Fairchilds Vorschlag so zu konvertieren, dass er mit Zeichenketten funktioniert, die Leerzeichen und XML-Sonderzeichen enthalten ( & , < , > ), die kodiert wurden.

Die endgültige Version meines Codes (die zwar nicht die ursprüngliche Frage beantwortet, aber vielleicht für jemanden nützlich ist) sieht wie folgt aus:

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

Anstatt ein Leerzeichen als Trennzeichen zu verwenden und alle Leerzeichen durch Kommas zu ersetzen, werden einfach ein Komma und ein Leerzeichen an jeden Wert angehängt und dann STUFF um die ersten beiden Zeichen zu entfernen.

Die XML-Kodierung wird automatisch durch die Verwendung der TYP Richtlinie.

Answer 5

Eine weitere Option mit Sql Server 2005 und höher

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