webentwicklung-frage-antwort-db.com.de

Wie verwende ich GROUP BY, um Zeichenfolgen in SQL Server zu verketten?

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
sqlsql-serverstring-concatenationsql-server-group-concat
345
Eldila

Keine CURSOR-, WHILE-Schleife oder benutzerdefinierte Funktion erforderlich.

Sie müssen nur mit FOR XML und PATH kreativ sein.

[Hinweis: Diese Lösung funktioniert nur in 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
517
Kevin Fairchild

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

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

wenn Sie einen XML-Pfad verwenden, wird er nicht perfekt verkettet, wie Sie es vielleicht erwarten. Er wird "" durch "amp;" ersetzen. und wird sich auch mit <" and "> anlegen ... vielleicht ein paar andere Dinge, nicht sicher ... aber du kannst es versuchen

Ich habe eine Problemumgehung gefunden ... Sie müssen Folgendes ersetzen:

FOR XML PATH('')
)

mit:

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

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

warum zum Teufel hat SQL keine verkettete Aggregatfunktion? Das ist eine PITA.

49
Allen

Ich hatte einige Probleme, als ich versuchte, den Vorschlag von Kevin Fairchild in Zeichenfolgen mit Leerzeichen und speziellen XML-Zeichen (&, <, >) umzuwandeln, die codiert waren.

Die endgültige Version meines Codes (der die ursprüngliche Frage nicht beantwortet, aber für jemanden nützlich sein kann) sieht folgendermaßen 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 jedem Wert lediglich ein Komma und ein Leerzeichen vorangestellt. Anschließend werden mit STUFF die ersten beiden Zeichen entfernt.

Die XML-Codierung wird automatisch mithilfe der Direktive TYPE vorgenommen.

38
Jonathan Sayce

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

Installieren Sie die SQLCLR-Aggregate von http://groupconcat.codeplex.com

Dann können Sie Code wie diesen schreiben, um das gewünschte Ergebnis zu erhalten:

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

In SQL Server 2005 und höher können Sie Ihre eigenen benutzerdefinierten Aggregatfunktionen erstellen, einschließlich für Dinge wie Verkettung - siehe das Beispiel am Ende des verlinkten Artikels.

12
Joel Coehoorn

Acht Jahre später ... Microsoft SQL Server vNext-Datenbankmodul hat Transact-SQL endlich so erweitert, dass es die Verkettung gruppierter Zeichenfolgen direkt unterstützt. In der Community Technical Preview Version 1.0 wurde die STRING_AGG-Funktion und in CTP 1.1 die WITHIN GROUP-Klausel für die STRING_AGG-Funktion hinzugefügt.

Referenz: https://msdn.Microsoft.com/en-us/library/mt775028.aspx

10
Shem Sargent

Ein Beispiel wäre

In Oracle können Sie die LISTAGG-Aggregatfunktion verwenden.

rsprüngliche Aufzeichnungen

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

Sql

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

Ergebnis in

name   type
------------
name1  type1
name2  type2; type3
10
Michal B.

Diese Art von Frage wird hier sehr oft gestellt, und die Lösung wird stark von den zugrunde liegenden Anforderungen abhängen:

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

und

https://stackoverflow.com/search?q=sql+concatenate

In der Regel gibt es keine SQL-reine Möglichkeit, dies ohne dynamisches SQL, eine benutzerdefinierte Funktion oder einen Cursor zu tun.

7
Cade Roux

Um Cade noch etwas hinzuzufügen: Dies ist normalerweise eine Front-End-Anzeige und sollte daher dort gehandhabt werden. Ich weiß, dass es manchmal einfacher ist, etwas zu 100% in SQL zu schreiben, zum Beispiel für den Dateiexport oder andere "SQL only" -Lösungen. Meistens sollte diese Verkettung jedoch in Ihrer Anzeigeebene behandelt werden.

7
Tom H

Dies ist nur eine Ergänzung zu Kevin Fairchilds Post (übrigens sehr clever). Ich hätte es als Kommentar hinzugefügt, aber ich habe noch nicht genug Punkte :)

Ich habe diese Idee für eine Ansicht verwendet, an der ich gearbeitet habe, aber die Elemente, die ich verkettet habe, enthielten Leerzeichen. Deshalb habe ich den Code leicht modifiziert, um keine Leerzeichen als Begrenzer zu verwenden.

Nochmals vielen Dank für die coole Lösung, 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
Phillip

Benötige keinen Cursor ... eine while-Schleife ist ausreichend.

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

Lassen Sie uns sehr einfach werden:

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

Ersetzen Sie diese Zeile:

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

Mit Ihrer Anfrage.

4
Marquinho Peli

es wurden keine zutreffenden Kreuzantworten angezeigt, und es ist auch keine XML-Extraktion erforderlich. Hier ist eine etwas andere Version dessen, was Kevin Fairchild geschrieben hat. Es ist schneller und einfacher in komplexeren Abfragen zu verwenden:

   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
Mordechai

Sie können die Leistung auf folgende Weise erheblich verbessern, wenn die Gruppierung nach hauptsächlich ein Element enthält:

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
Eduard

Verwenden von Replace Function und FOR JSON PATH

SELECT T3.DEPT, REPLACE(REPLACE(T3.ENAME,'{"ENAME":"',''),'"}','') AS ENAME_LIST
FROM (
 SELECT DEPT, (SELECT ENAME AS [ENAME]
        FROM EMPLOYEE T2
        WHERE T2.DEPT=T1.DEPT
        FOR JSON PATH,WITHOUT_ARRAY_WRAPPER) ENAME
    FROM EMPLOYEE T1
    GROUP BY DEPT) T3

Beispieldaten und weitere Möglichkeiten hier klicken

1
Mahesh