如何使用GROUP BY连接SQL Server中的字符串？

Question

如何获得：

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

到

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

Answer 1

没有光标，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 

Answer 2

这类问题是经常在这里问，该解决方案将依赖大量的潜在需求：

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

and

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

通常，在没有动态sql，用户定义的函数或游标的情况下，不存在仅使用SQL的方法。

Answer 3

只是为了增加凯德所说的，这通常是一个前端显示的东西，因此应该在那里处理。我知道，有时在SQL中为文件导出或其他“仅SQL”解决方案写入100％的东西比较容易，但大多数情况下，这种串联应该在显示层处理。

Answer 4

不需要光标... 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 

Answer 5

SQL Server 2005和更高版本允许你创建自己的custom aggregate functions，包括搞什么concatenation-看到样品在链接的文章的底部。

Answer 6

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

Answer 7

这仅仅是一个除了凯文飞兆半导体后（顺便说一句非常聪明）的另一种选择。我会添加它作为评论，但我还没有足够的积分:)

我正在使用这个想法我正在处理的视图，但是我concudyating项目包含空格。所以我稍微修改了代码，不使用空格作为分隔符。

再次感谢非常酷的解决方法凯文！

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 

Answer 8

我遇到了几个问题，当我试图转换凯文飞兆半导体的建议包含空格和被编码的特殊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编码通过使用TYPE指令自动处理。

Answer 9

使用XML路径将不会像您所期望的那样完美连接......它会将“&”替换为“& amp”并且还将惹<" and "> ......也许其他一些事情，不知道...但你可以试试这个

我碰到一个解决办法来为这个......你需要更换：

FOR XML PATH('') 
) 

有：

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

...或者NVARCHAR(MAX)如果那是你使用的是什么。

为什么地狱不SQL有一个连接集合函数？这是一个PITA。

Answer 10

在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 

Answer 11

让我们非常简单：

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 

与您查询。

Answer 12

可以提高性能显著以下方式，如果组由主要包含了一个项目：

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 

Answer 13

从http://groupconcat.codeplex.com

安装SQLCLR聚集然后，你可以写这样的代码来获取你问的结果为：

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; 

Answer 14

八年后...... Microsoft SQL Server vNext数据库引擎终于增强了Transact-SQL以直接支持ort分组字符串连接。社区技术预览版本1.0添加了STRING_AGG函数，并且CTP 1.1为STRING_AGG函数添加了WITHIN GROUP子句。

参考：https://msdn.microsoft.com/en-us/library/mt775028.aspx

Answer 15

没有看到任何跨应用的答案，也没有必要对XML提取。这是Kevin Fairchild写的一个稍微不同的版本。它的速度更快和更容易使用更复杂的查询：如果是SQL服务器2017年或SQL Server Vnext，SQL Azure中您可以使用如下string_agg

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 

Answer 16

：

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