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

Question

我该如何获得：

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

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

- Eldila

---

在MySQL中，可以使用GROUP_CONCAT()聚合函数轻松解决这种问题，但在Microsoft SQL Server上解决这个问题则比较麻烦。需要帮助的话可以参考以下Stack Overflow的问题： "如何基于关系获取一个记录的多个记录？" - Bill Karwin

每个拥有微软账户的人都应该在Connect上投票支持更简单的解决方案：https://connect.microsoft.com/SQLServer/feedback/details/427987/olap-function-for-string-concatenation - Jens Mühlenhoff

您可以使用此处找到的SQLCLR聚合函数作为替代，直到T-SQL得到增强：http://groupconcat.codeplex.com - Orlando Colamatteo

将多行连接成单个文本字符串 - Salman A

Answer 1

不需要使用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

- Kevin Fairchild

---

为什么有人会对临时表使用NOLOCK？ - Amy B

嗯，我只是讨厌它的子查询风格。使用联接更好。只是不认为我可以在这个解决方案中利用它。不管怎样，我很高兴看到这里还有其他像我一样喜欢学习这种东西的SQL迷。向你们致敬 :) - Kevin Fairchild

一种稍微更清晰的字符串操作方法：将(SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH (''))的结果传递给STUFF函数，并指定在第一个字符位置开始删除两个字符，即使用1,2,'')参数。最终得到的结果是NameValues。 - Jonathan Sayce

我认为对于SQL Server来说，这是解决这个问题的唯一方法，没有使用变量会令人尴尬。 - Jim Pedid

这其实更像是一个解决方案而不是答案，如果你有一个非常大的查询，并且没有易于使用的ID列，在视图中有许多+连接+子查询+分组条件，该怎么办？把整个查询复制粘贴到“FOR XML”子句中（对于每个你想要连接的列），这难道真的是SQL Server所提供的最好的解决方案吗？我认为真正的答案是直到2017年，SQL Server并没有本地支持字符串连接。非常令人失望 :( - David Rogers

Answer 2

如果是 SQL Server 2017 或 SQL Server Vnext，SQL Azure 可以使用以下的 STRING_AGG:

SELECT id, STRING_AGG(CONCAT(name, ':', [value]), ', ')
FROM #YourTable 
GROUP BY id

- Kannan Kandasamy

---

热死了！我还得加上额外的字才能让评论够长，但实际上前两个字就是我想说的。 - undefined

Answer 3

使用XML路径拼接文本时，并不能像您期望的那样完美地连接...它会将"&"替换为"&"，并且还会破坏<和>等标签。可能还有其他一些问题，不确定...但您可以尝试这个方法：

我发现了一个解决方法...你需要将以下内容替换：

FOR XML PATH('')
)

使用：

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

如果您正在使用NVARCHAR(MAX)，则可以使用FOR XML PATH('')进行字符串连接。

为什么SQL没有连接聚合函数？这真是太让人头疼了。

- Allen

---

我已经搜索了整个网络，寻找最佳的方法来避免对输出进行编码。非常感谢！这是最终的答案 - 直到微软添加适当的支持，如CONCAT()聚合函数。我的做法是将其放入一个Outer-Apply中，返回我的连接字段。我不喜欢在我的选择语句中添加嵌套选择。 - MikeTeeVee

我同意，如果不使用Value，我们可能会遇到文本为XML编码字符的问题。请查看我的博客，其中涵盖了SQL Server中分组连接的场景。 https://blog.vcillusion.co.in/understanding-the-grouped-concatenation-sql-server/ - vCillusion

Answer 4

当我尝试将Kevin Fairchild的建议应用于包含空格和特殊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删除前两个字符。

通过使用TYPE指令自动处理XML编码。

- Jonathan Sayce

Answer 5

另一种选择是使用 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

- cyberkiwi

---

感谢您的输入，我总是更喜欢使用公共表表达式和递归公共表表达式来解决SQL Server中的问题。这个方法对我非常有效！ - gbdavid

能否在查询中使用outer apply？ - fire in the hole

Answer 6

八年后…微软 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

- Shem Sargent

Answer 7

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

- Orlando Colamatteo

---

我几年前用过它，语法比所有的“XML Path”技巧都要干净得多，并且它运行得非常好。当 SQL CLR 函数是一个选项时，我强烈推荐使用它。 - AFract

Answer 8

SQL Server 2005及其后版本可以创建自定义聚合函数，例如连接字符串（concatenation）-请参阅链接文章底部的示例。custom aggregate functions

- Joel Coehoorn

---

不幸的是，这需要使用CLR程序集...这又是另一个需要处理的问题 :-/ - user166390

示例仅使用CLR进行实际的连接实现，但这并非必需。您可以使连接聚合函数使用FOR XML，以便将来调用更加整洁！ - Shiv

Answer 9

这只是对Kevin Fairchild的帖子的补充（非常聪明）。我本来想将其作为评论添加，但我还没有足够的积分 :)

我正在使用这个想法来处理一个视图，但是我要连接的项包含空格。因此，我稍微修改了代码，不使用空格作为分隔符。

再次感谢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 

- Phillip

Answer 10

这种问题在这里经常被问到，解决方案将取决于基本要求。

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

并且

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

通常情况下，没有仅使用SQL的方法来完成此操作，需要使用动态SQL、用户定义函数或游标。

- Cade Roux

---

不是真的。cyberkiwi使用cte:s的解决方案是纯SQL，没有任何特定于供应商的技巧。 - Björn Lindqvist

在问答时，我不会认为递归CTE非常便携，但现在Oracle已经支持它们了。最好的解决方案将取决于平台。对于SQL Server来说，最可能的解决方案是使用FOR XML技术或自定义CLR聚合函数。 - Cade Roux

所有问题的终极答案？http://stackoverflow.com/search?q=[无论什么问题] - Junchen Liu