Jak używać GROUP BY do łączenia łańcuchów w SQL Server?


373

Jak mogę uzyskać:

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

do

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

18
Ten typ problemu można łatwo rozwiązać na MySQL za pomocą GROUP_CONCAT()funkcji agregującej, ale rozwiązanie go na Microsoft SQL Server jest trudniejsze. Aby uzyskać pomoc, zobacz następujące pytanie SO: „ Jak uzyskać wiele rekordów w stosunku do jednego rekordu na podstawie relacji?
Bill Karwin,

1
Każdy z kontem Microsoft powinien głosować na prostsze rozwiązanie na connect: connect.microsoft.com/SQLServer/feedback/details/427987/...
Jens Mühlenhoff

1
Można użyć znalezionych tutaj agregatów SQLCLR jako zamienników do czasu ulepszenia języka
Orlando Colamatteo

Odpowiedzi:


550

Nie jest wymagana CURSOR, WHILE loop ani funkcja zdefiniowana przez użytkownika .

Wystarczy być kreatywnym z FOR XML i PATH.

[Uwaga: To rozwiązanie działa tylko w SQL 2005 i nowszych wersjach. Oryginalne pytanie nie określało używanej wersji.]

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

6
dlaczego nie nolock tabeli tymczasowej?
Amy B,

3
To najfajniejsza rzecz SQL, jaką widziałem w życiu. Masz pomysł, czy jest „szybki” dla dużych zestawów danych? Nie zaczyna się czołgać jak kursor czy coś, prawda? Chciałbym, żeby więcej ludzi głosowało za tym szaleństwem.
user12861,

6
Eh Nienawidzę stylu tego zapytania. DOŁĄCZENIA są o wiele milsze. Tylko nie sądzę, że mogę to wykorzystać w tym rozwiązaniu. Tak czy inaczej, cieszę się, że oprócz mnie lubią uczyć się takich rzeczy, oprócz innych dorków SQL. Uznanie dla wszystkich :)
Kevin Fairchild

6
Nieco czystszy sposób wykonywania operacji na łańcuchach: STUFF ((WYBIERZ), „+ [Nazwa] +”: „+ CAST ([Wartość] JAKO VARCHAR (MAKS.)) OD #YourTable GDZIE (ID = Results.ID) DLA XML PATH ('')), 1,2, '') AS NameValues
Jonathan Sayce

3
Aby odnotować coś, co znalazłem. Nawet w środowisku bez rozróżniania wielkości liter, .value część zapytania POTRZEBUJE być małymi literami. Zgaduję, że dzieje się tak, ponieważ jest to XML, w którym rozróżniana jest
wielkość

136

Jeśli jest to SQL Server 2017 lub SQL Server Vnext, SQL Azure możesz użyć string_agg jak poniżej:

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

Działa bezbłędnie!
argoo,

1
Działa to świetnie, lepiej niż zaakceptowana odpowiedź.
Jannick Breunis

51

użycie ścieżki XML nie będzie idealnie konkatenować, jak można się spodziewać ... zastąpi „&” na „& amp;” i będzie również bałagan z <" and "> ... może kilkoma innymi rzeczami, nie jestem pewien ... ale możesz spróbować

Natrafiłem na obejście tego ... musisz zastąpić:

FOR XML PATH('')
)

z:

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

... lub NVARCHAR(MAX)jeśli tego właśnie używasz.

dlaczego, do diabła, nie SQLma konkatenowanej funkcji agregującej? to jest PITA.


2
Przeszukałem sieć, szukając najlepszego sposobu NIE zakodowania wyjścia. Dziękuję bardzo! To jest ostateczna odpowiedź - dopóki MS nie doda do tego odpowiedniego wsparcia, takiego jak funkcja agregująca CONCAT (). To, co robię, to wrzucam to do zewnętrznego zastosowania, które zwraca moje połączone pole. Nie jestem fanem dodawania zagnieżdżonych selekcji do moich instrukcji select.
MikeTeeVee,

Zgodziłem się, że bez użycia wartości możemy napotykać problemy, w których tekst jest znakiem zakodowanym w formacie XML. Proszę znaleźć mój blog obejmujący scenariusze grupowania konkatenacji na serwerze SQL. blog.vcillusion.co.in/…
vCillusion

40

Wpadłem na kilka problemów gdy próbowałem konwersji sugestię Kevin Fairchild do pracy z ciągi zawierające spacje i znaki specjalne XML ( &, <, >), które zostały zakodowane.

Ostateczna wersja mojego kodu (która nie odpowiada na oryginalne pytanie, ale może być dla kogoś przydatna) wygląda następująco:

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

Zamiast używać spacji jako separatora i zastępując wszystkie spacje przecinkami, po prostu uprzednio wstawia przecinek i spację do każdej wartości, a następnie używa STUFFdo usunięcia pierwszych dwóch znaków.

Kodowanie XML jest obsługiwane automatycznie przy użyciu dyrektywy TYPE .


21

Inna opcja korzystająca z Sql Server 2005 i nowszych

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

Dzięki za wkład, zawsze wolę używać CTE i rekurencyjnych CTE do rozwiązywania problemów na serwerze SQL. To działa, jeden działa dla mnie świetnie!
gbdavid

czy można go użyć w zapytaniu z zewnętrznym zastosowaniem?
strzelić do dołka

14

Zainstaluj SQLCLR Aggregates ze strony http://groupconcat.codeplex.com

Następnie możesz napisać taki kod, aby uzyskać wynik, o który prosiłeś:

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;

Użyłem go kilka lat temu, składnia jest znacznie czystsza niż wszystkie triki „Ścieżki XML” i działa bardzo dobrze. Zdecydowanie polecam, gdy dostępne są funkcje SQL CLR.
AFract

12

SQL Server 2005 i nowsze wersje umożliwiają tworzenie własnych niestandardowych funkcji agregujących , w tym takich jak konkatenacja - zobacz przykład na dole połączonego artykułu.


4
Niestety wymaga to (?) Korzystania z zestawów CLR. Jest to kolejny problem do rozwiązania: - /

1
Tylko przykład używa CLR do faktycznej implementacji konkatenacji, ale nie jest to wymagane. Możesz włączyć funkcję agregacji konkatenacji dla formatu XML, aby przynajmniej w przyszłości lepiej było ją wywoływać!
Shiv

12

Osiem lat później ... Aparat bazy danych Microsoft SQL Server vNext w końcu ulepszył Transact-SQL, aby bezpośrednio obsługiwał zgrupowanie łańcuchów ciągów. Community Technical Preview wersja 1.0 dodała funkcję STRING_AGG, a CTP 1.1 dodała klauzulę WITHIN GROUP dla funkcji STRING_AGG.

Odniesienie: https://msdn.microsoft.com/en-us/library/mt775028.aspx


9

To tylko dodatek do postu Kevina Fairchilda (bardzo sprytnie przy okazji). Dodałbym to jako komentarz, ale nie mam jeszcze wystarczającej liczby punktów :)

Użyłem tego pomysłu do widoku, nad którym pracowałem, jednak przedmioty, które konatynizowałem, zawierały spacje. Zmodyfikowałem więc nieco kod, aby nie używać spacji jako ograniczników.

Jeszcze raz dziękuję za fajne obejście 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 

9

Przykładem może być

W Oracle można użyć funkcji agregującej LISTAGG.

Oryginalne rekordy

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

Sql

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

Skutkować w

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

6
Wygląda ładnie, ale pytania nie dotyczą konkretnie Oracle.
user12861

13
Rozumiem. Ale szukałem tego samego dla Oracle, więc pomyślałem, że umieszczę to tutaj dla innych ludzi takich jak ja :)
Michał B.,

@MichalB. Nie brakuje ci wewnętrznej składni? np .: listagg (typ, ',') w grupie (kolejność według nazwy)?
gregory,

@gregory: Zredagowałem swoją odpowiedź. Myślę, że moje stare rozwiązanie kiedyś działało. Obecny formularz, który zasugerowałeś, na pewno zadziała, dzięki.
Michał B.

1
dla przyszłych ludzi - możesz napisać nowe pytanie z własną odpowiedzią na znaczącą różnicę, taką jak inna platforma
Mike M

7

Tego rodzaju pytanie zadawane jest tutaj bardzo często, a rozwiązanie będzie w dużej mierze zależeć od podstawowych wymagań:

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

i

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

Zwykle nie ma sposobu, aby to zrobić tylko w języku SQL bez dynamicznego SQL, funkcji zdefiniowanej przez użytkownika lub kursora.


2
Nie prawda. Rozwiązanie cyberkiwi wykorzystujące cte: s to czysta sql bez żadnych działań hakerskich specyficznych dla dostawcy.
Björn Lindqvist

1
W momencie pytania i odpowiedzi nie liczyłbym rekurencyjnych CTE jako strasznie przenośnych, ale teraz są obsługiwane przez Oracle. Najlepsze rozwiązanie będzie zależeć od platformy. W przypadku SQL Server najprawdopodobniej jest to technika FOR XML lub agregacja CLR klienta.
Cade Roux,

1
ostateczna odpowiedź na wszystkie pytania? stackoverflow.com/search?q=[takie pytanie]
Junchen Liu

7

Żeby dodać do tego, co powiedział Cade, jest to zwykle wyświetlacz front-end i dlatego należy się nim zająć. Wiem, że czasami łatwiej jest napisać coś w 100% w języku SQL dla takich rzeczy jak eksport plików lub inne rozwiązania „tylko SQL”, ale w większości przypadków ta konkatenacja powinna być obsługiwana w warstwie wyświetlania.


11
Grupowanie jest teraz czymś front-endowym? Istnieje wiele prawidłowych scenariuszy łączenia jednej kolumny w zgrupowanym zestawie wyników.
MGOwen

5

Nie potrzebujesz kursora ... wystarczy pętla while.

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


@marc_s być może lepszą krytyką jest to, że KLUCZ PODSTAWOWY powinien być zadeklarowany na zmiennych tabeli.
Amy B

@marc_s Po dalszej inspekcji ten artykuł jest fikcją - podobnie jak prawie wszystkie dyskusje na temat wydajności bez pomiaru IO. Dowiedziałem się o LGD - dziękuję za to.
Amy B

4

Zróbmy bardzo proste:

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

Zamień ten wiersz:

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

Z twoim zapytaniem.


3

nie widziałem odpowiedzi krzyżowych, również nie ma potrzeby ekstrakcji xml. Oto nieco inna wersja tego, co napisał Kevin Fairchild. Jest szybszy i łatwiejszy w użyciu w bardziej złożonych zapytaniach:

   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

1
Bez użycia wartości możemy napotkać problemy, w których tekst jest znakiem zakodowanym w formacie XML
vCillusion

2

Możesz poprawić znaczącą wydajność w następujący sposób, jeśli grupa według zawiera głównie jeden element:

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

Zakładając, że nie chcesz duplikować nazw na liście, które możesz lub nie.
jnm2

1

Korzystanie z funkcji Zamień i 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

Aby uzyskać przykładowe dane i więcej sposobów, kliknij tutaj


Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.