Как использовать GROUP BY для объединения строк в SQL Server?

Question

Как использовать GROUP BY для объединения строк в SQL Server?

Как добраться:

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

до

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

16 299

sql sql-server string-concatenation sql-server-group-concat

16 ответов:

использование XML-пути не будет идеально объединяться, как вы могли бы ожидать... он заменит " & "на"&", а также будет возиться с <" and "> ...может быть, несколько других вещей, не уверен...но вы можете попробовать это

я наткнулся на обходной путь для этого... вам нужно заменить:
FOR XML PATH('')
)
С:
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')
...или NVARCHAR(MAX) если это то, что вы используете.

почему, черт возьми, нет SQL есть объединенная агрегатная функция? это Пита.

45

я столкнулся с несколькими проблемами, когда попытался преобразовать предложение Кевина Фэрчайлда для работы со строками, содержащими пробелы и специальные символы XML (&,<,>), которые были закодированы.

финальная версия моего кода (который не отвечает на исходный вопрос, но может быть кому-то пригодится) выглядит так:
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
вместо того, чтобы использовать пробел в качестве разделителя и заменять все пробелы запятыми, он просто предваряет запятую и Пробел, чтобы затем каждое значение использует STUFF для удаления первых двух символов.

кодировка XML выполняется автоматически с помощью тип

33

Если это SQL Server 2017 или SQL Server Vnext, SQL Azure можно использовать string_agg, как показано ниже:
select id, string_agg(concat(name, ':', [value]), ', ')
    from #YourTable 
    group by id

26

другой вариант с использованием Sql Server 2005 и выше

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

установите агрегаты SQLCLR из http://groupconcat.codeplex.com

затем вы можете написать такой код, чтобы получить результат, который вы просили:
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

SQL Server 2005 и более поздние версии позволяют создавать свои собственные пользовательские агрегатные функции, в том числе для таких вещей, как конкатенация - см. Образец в нижней части связанной статьи.

12

восемь лет спустя... Microsoft SQL Server vNext Database Engine наконец-то улучшил Transact-SQL для непосредственной поддержки объединения сгруппированных строк. В технической предварительной версии сообщества 1.0 добавлена функция STRING_AGG, а CTP 1.1 добавила предложение WITHIN GROUP для функции STRING_AGG.

Ссылка:https://msdn.microsoft.com/en-us/library/mt775028.aspx

9

просто чтобы добавить к тому, что сказал Кейд, это обычно внешний дисплей вещь и поэтому должны быть обработаны там. Я знаю, что иногда проще написать что-то 100% в SQL для таких вещей, как экспорт файлов или другие решения "только для SQL", но в большинстве случаев эта конкатенация должна обрабатываться в вашем слое отображения.

7

в Oracle можно использовать агрегатную функцию LISTAGG. Примером может быть:
name   type
------------
name1  type1
name2  type2
name2  type3

SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name
в результате:
name   type
------------
name1  type1
name2  type2; type3

7

этот вопрос задается здесь очень часто, и решение будет во многом зависеть от основных требований:

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

и

https://stackoverflow.com/search?q=sql + конкатенация

Как правило, нет SQL-только способ сделать это без динамического sql, определяемой пользователем функции или курсора.

6

Это просто дополнение к посту Кевина Фэрчайлда (очень умный, кстати). Я бы добавил его в качестве комментария, но у меня пока недостаточно очков :)

я использовал эту идею для представления, над которым я работал, однако элементы, которые я объединял, содержали пробелы. Поэтому я немного изменил код, чтобы не использовать пробелы в качестве разделителей.

еще раз спасибо за классный обходной путь Кевин!
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

не нужен курсор... цикл 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

5

давайте очень просто:
SELECT stuff(
    (
    select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb 
    FOR XML PATH('')
    )
, 1, 2, '')
замените эту строку:
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
с запросом.

4

не видел никаких перекрестных ответов, также нет необходимости в извлечении xml. Вот немного другая версия того, что написал Кевин Фэрчайлд. Это быстрее и проще использовать в более сложных запросах:
   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

вы можете значительно повысить производительность следующим образом, если group by содержит в основном один элемент:
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

Kevin Fairchild · Accepted Answer · 2013-07-29 22:15:38

нет курсора, в то время как цикл или пользовательская функция не требуется.

просто нужно быть творческим с for XML и PATH.

[Примечание: это решение работает только на SQL 2005 и более поздних. Оригинальный вопрос не указал версию в использовании.]
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