如何使用 GROUP BY 在 SQL Server 中连接字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/273238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use GROUP BY to concatenate strings in SQL Server?
提问by Eldila
How do I get:
如何得到：
id Name Value
1 A 4
1 B 8
2 C 9
to
到
id Column
1 A:4, B:8
2 C:9
回答by Kevin Fairchild
No CURSOR, WHILE loop, or User-Defined Function needed.
不需要 CURSOR、WHILE 循环或用户定义的函数。
Just need to be creative with FOR XML and PATH.
只需要对 FOR XML 和 PATH 发挥创意。
[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]
[注意：此解决方案仅适用于 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
回答by Kannan Kandasamy
If it is SQL Server 2017 or SQL Server Vnext, SQL Azure you can use string_agg as below:
如果是 SQL Server 2017 或 SQL Server Vnext, SQL Azure 你可以使用 string_agg 如下：
select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable
group by id
回答by Allen
using XML path will not perfectly concatenate as you might expect... it will replace "&" with "&" and will also mess with <" and ">...maybe a few other things, not sure...but you can try this
使用 XML 路径不会像您预期的那样完美连接……它将用“&”替换“&” 并且还会弄乱<" and ">......也许还有其他一些事情，不确定......但你可以试试这个
I came across a workaround for this... you need to replace:
我遇到了一个解决方法......你需要更换：
FOR XML PATH('')
)
with:
和：
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')
...or NVARCHAR(MAX)if thats what youre using.
...或者NVARCHAR(MAX)如果那是你正在使用的。
why the hell doesn't SQLhave a concatenate aggregate function? this is a PITA.
为什么地狱没有SQL连接聚合函数？这是一个皮塔饼。
回答by Jonathan Sayce
I ran into a couple of problems when I tried converting Kevin Fairchild's suggestion to work with strings containing spaces and special XML characters (&, <, >) which were encoded.
当我尝试将 Kevin Fairchild 的建议转换为使用包含空格和特殊 XML 字符（&, <, >）的字符串时，我遇到了一些问题。
The final version of my code (which doesn't answer the original question but may be useful to someone) looks like this:
我的代码的最终版本（它没有回答原始问题，但可能对某人有用）如下所示：
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
Rather than using a space as a delimiter and replacing all the spaces with commas, it just pre-pends a comma and space to each value then uses STUFFto remove the first two characters.
它不是使用空格作为分隔符并用逗号替换所有空格，而是在每个值前添加一个逗号和空格，然后用于STUFF删除前两个字符。
The XML encoding is taken care of automatically by using the TYPEdirective.
XML 编码是通过使用TYPE指令自动处理的。
回答by cyberkiwi
Another option using Sql Server 2005 and above
使用 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
回答by Orlando Colamatteo
Install the SQLCLR Aggregates from http://groupconcat.codeplex.com
从http://groupconcat.codeplex.com安装 SQLCLR 聚合
Then you can write code like this to get the result you asked for:
然后你可以写这样的代码来得到你要求的结果：
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;
回答by Shem Sargent
Eight years later... Microsoft SQL Server vNext Database Engine has finally enhanced Transact-SQL to directly support grouped string concatenation. The Community Technical Preview version 1.0 added the STRING_AGG function and CTP 1.1 added the WITHIN GROUP clause for the STRING_AGG function.
八年后... Microsoft SQL Server vNext 数据库引擎终于增强了 Transact-SQL 以直接支持分组字符串连接。Community Technical Preview 1.0 版添加了 STRING_AGG 函数，CTP 1.1 为 STRING_AGG 函数添加了 WITHIN GROUP 子句。
Reference: https://msdn.microsoft.com/en-us/library/mt775028.aspx
回答by Joel Coehoorn
SQL Server 2005 and later allow you to create your own custom aggregate functions, including for things like concatenation- see the sample at the bottom of the linked article.
SQL Server 2005 及更高版本允许您创建自己的自定义聚合函数，包括诸如连接之类的事情 - 请参阅链接文章底部的示例。
回答by Phillip
This is just an addition to Kevin Fairchild's post (very clever by the way). I would have added it as a comment, but I don't have enough points yet :)
这只是对 Kevin Fairchild 帖子的补充（顺便说一下，这很聪明）。我会把它添加为评论，但我还没有足够的分数:)
I was using this idea for a view I was working on, however the items I was concatinating contained spaces. So I modified the code slightly to not use spaces as delimiters.
我将这个想法用于我正在处理的视图，但是我连接的项目包含空间。所以我稍微修改了代码，不使用空格作为分隔符。
Again thanks for the cool workaround 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
回答by Michal B.
An example would be
一个例子是
In Oracle you can use LISTAGG aggregate function.
在 Oracle 中，您可以使用 LISTAGG 聚合函数。
Original records
原始记录
name type
------------
name1 type1
name2 type2
name2 type3
Sql
数据库
SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name
Result in
导致
name type
------------
name1 type1
name2 type2; type3
