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

Użycie ścieżki XML nie będzie idealnie połączone, jak można się spodziewać... zastąpi " & " na " & " i będzie również bałagan z <" and "> ...może kilka innych rzeczy, nie jestem pewien...ale możesz spróbować tego

FOR XML PATH('')
)

Z:

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

...lub NVARCHAR(MAX) jeśli tego używasz.

Dlaczego do cholery nie ma SQL funkcji agregatu konkatenacyjnego? to jest PITA.

Napotkałem kilka problemów, gdy próbowałem przekonwertować sugestię Kevina Fairchilda do pracy z ciągami zawierającymi spacje i specjalne znaki XML(&, <, >) które zostały zakodowane.

Finalna wersja mojego kodu (która nie odpowiada na oryginalne pytanie, ale może komuś się przydać) wygląda tak:

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 ogranicznika i zastępować wszystkie spacje przecinkami, po prostu dodaje przecinek i spację do każdej wartości, a następnie używa STUFF aby usunąć dwa pierwsze znaki.

Kodowanie XML jest obsługiwane automatycznie za pomocą dyrektywy TYPE.

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

Inna opcja przy użyciu 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

Zainstaluj Agregaty SQLCLR z http://groupconcat.codeplex.com

Następnie możesz napisać kod w ten sposób, 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;

SQL Server 2005 i nowsze pozwalają na tworzenie własnych niestandardowych funkcji agregacyjnych , w tym dla rzeczy takich jak konkatenacja-zobacz próbkę na dole połączonego artykułu.

Osiem lat później... Microsoft SQL Server vNext Database Engine wreszcie ulepszył Transact-SQL, aby bezpośrednio obsługiwać zgrupowane łączenie łańcuchów. Wersja Community Technical Preview 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

Aby dodać do tego, co powiedział Cade, zwykle jest to wyświetlacz front-end i dlatego powinien być tam obsługiwany. Wiem, że czasami łatwiej jest napisać coś w 100% W SQL dla rzeczy takich jak eksport plików lub inne rozwiązania "tylko SQL" , ale większość razy ta konkatenacja powinna być obsługiwana w warstwie wyświetlania.

W Oracle możesz użyć funkcji LISTAGG aggregate. Przykładem może być:

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

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

Spowodowałoby:

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

Tego rodzaju pytanie jest tutaj zadawane 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 + konkatenat

Zazwyczaj nie ma sposobu na to tylko SQL bez dynamicznego sql, funkcji zdefiniowanej przez użytkownika lub kursora.

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

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

Jeszcze raz dzięki 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 

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

Bądźmy bardzo proste:

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

Zastąp tę linię:

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

Z Twoim zapytaniem.

Nie widziałem żadnych krzyżowych odpowiedzi, 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

Możesz znacznie poprawić wydajność w następujący sposób, jeśli group by 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