Как использовать 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
23 ответа
Не требуется CURSOR, цикл WHILE или пользовательская функция.
Просто нужно быть креативным с 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
Если это SQL Server 2017 или SQL Server Vnext, SQL Azure вы можете использовать string_agg, как показано ниже:
select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable
group by id
Использование пути XML не будет идеально объединено, как вы могли бы ожидать... оно заменит "&" на "& amp;" и будет также связываться с <" and ">... может быть, несколько других вещей, не уверен... но вы можете попробовать это
Я нашел обходной путь для этого... вам нужно заменить:
FOR XML PATH('')
)
с:
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')
...или же NVARCHAR(MAX) если это то, что вы используете.
почему, черт возьми, не SQL есть объединенная агрегатная функция? это пита
Я столкнулся с несколькими проблемами, когда попытался преобразовать предложение Кевина Фэйрчайлда для работы со строками, содержащими пробелы и специальные символы 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. > < etc*/
).value('.','VARCHAR(MAX)')
,1,2,'') as NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Вместо использования пробела в качестве разделителя и замены всех пробелов запятыми, он просто добавляет запятую и пробел к каждому значению, а затем использует STUFF удалить первые два символа.
Кодировка XML обеспечивается автоматически с помощью директивы TYPE.
Еще один вариант использования 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
Восемь лет спустя... Ядро базы данных Microsoft SQL Server vNext наконец-то усовершенствовало Transact-SQL для прямой поддержки конкатенации сгруппированных строк. В техническом обзоре сообщества версии 1.0 добавлена функция STRING_AGG, а в CTP 1.1 добавлено предложение WITHIN GROUP для функции STRING_AGG.
Ссылка: https://msdn.microsoft.com/en-us/library/mt775028.aspx
Установите агрегаты 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;
SQL Server 2005 и более поздние версии позволяют создавать собственные настраиваемые агрегатные функции, в том числе для таких вещей, как конкатенация - см. Образец внизу связанной статьи.
В 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
Это всего лишь дополнение к посту Кевина Фэйрчайлда (кстати, очень умный). Я бы добавил это как комментарий, но у меня пока недостаточно очков:)
Я использовал эту идею для представления, над которым работал, однако элементы, которые я объединял, содержали пробелы. Поэтому я немного изменил код, чтобы не использовать пробелы в качестве разделителей.
Еще раз спасибо за классный обходной путь, Кевин!
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
Этот тип вопросов здесь задают очень часто, и решение будет во многом зависеть от базовых требований:
https://stackru.com/search?q=sql+pivot
а также
https://stackru.com/search?q=sql+concatenate
Как правило, нет единственного SQL-способа сделать это без динамического sql, пользовательской функции или курсора.
Просто чтобы добавить к тому, что сказал Кейд, это, как правило, интерфейсная штука, и поэтому она должна обрабатываться там. Я знаю, что иногда проще написать что-то на 100% в SQL для таких вещей, как экспорт файлов или другие решения "только для SQL", но в большинстве случаев эта конкатенация должна обрабатываться на вашем уровне отображения.
Не нужен курсор... цикла 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
Давайте получим очень просто:
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
С вашим запросом.
Использование оператора Stuff and for xml path для объединения строк в строку:Group By two columns ->
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 (1,'B',5)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
-- retrieve each unique id and name columns and concatonate the values into one column
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES EACH APPLICATION : VALUE SET
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
SELECT
[ID],[Name] , --these are acting as the group by clause
STUFF((
SELECT ', '+ CAST([Value] AS VARCHAR(MAX)) -- CONCATONATES THE VALUES FOR EACH ID NAME COMBINATION
FROM #YourTable
WHERE (ID = Results.ID and Name = results.[name] )
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID, name
DROP TABLE #YourTable
Вы можете значительно повысить производительность следующим образом, если группа по содержит в основном один элемент:
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
Не видел перекрестных ответов, также не нужно извлекать 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
Для всех моих ребят из здравоохранения:
SELECT
s.NOTE_ID
,STUFF ((
SELECT
[note_text] + ' '
FROM
HNO_NOTE_TEXT s1
WHERE
(s1.NOTE_ID = s.NOTE_ID)
ORDER BY [line] ASC
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,
1,
2,
'') AS NOTE_TEXT_CONCATINATED
FROM
HNO_NOTE_TEXT s
GROUP BY NOTE_ID
Если у вас включен clr, вы можете использовать библиотеку Group_Concat из GitHub
Использование функции замены и 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
Для образца данных и других способов нажмите здесь
Я использовал этот подход, который, возможно, легче понять. Получите корневой элемент, затем объедините его, чтобы выбрать любой элемент с тем же идентификатором, но не с `` официальным '' именем
Declare @IdxList as Table(id int, choices varchar(max),AisName varchar(255))
Insert into @IdxLIst(id,choices,AisName)
Select IdxId,''''+Max(Title)+'''',Max(Title) From [dbo].[dta_Alias]
where IdxId is not null group by IdxId
Update @IdxLIst
set choices=choices +','''+Title+''''
From @IdxLIst JOIN [dta_Alias] ON id=IdxId And Title <> AisName
where IdxId is not null
Select * from @IdxList where choices like '%,%'
Другой пример без мусора: ",TYPE).value('(./text())[1]','VARCHAR(MAX)')"
WITH t AS (
SELECT 1 n, 1 g, 1 v
UNION ALL
SELECT 2 n, 1 g, 2 v
UNION ALL
SELECT 3 n, 2 g, 3 v
)
SELECT g
, STUFF (
(
SELECT ', ' + CAST(v AS VARCHAR(MAX))
FROM t sub_t
WHERE sub_t.g = main_t.g
FOR XML PATH('')
)
, 1, 2, ''
) cg
FROM t main_t
GROUP BY g
Ввод-вывод есть
************************* -> *********************
* n * g * v * * g * cg *
* - * - * - * * - * - *
* 1 * 1 * 1 * * 1 * 1, 2 *
* 2 * 1 * 2 * * 2 * 3 *
* 3 * 2 * 3 * *********************
*************************
Здравствуйте, теперь можно объединить строки в группу с помощью SQL Server, используя функцию STRING_AGG. Вот пример
--- Создать таблицу
create table test_string_agg(
id int,
str_text varchar(100)
)
-- Вставлять
insert into test_string_agg
values (1,'Text1'),(1,'Text2'),(1,'Text3'), (2,'Text4')
--Запрос
select id, STRING_AGG(str_text, ';') as t_string_agg from test_string_agg
group by id
--Результат
-------------------------------
| id | t_string_agg
----------------------------------
| 1 | Text1;Text2;Text3
---------------------------------
| 2 | Text4
-----------------------------------