SQL 如何连接每个组的某个列中的所有字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6579440/
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 concatenate all strings from a certain column for each group
提问by yonan2236
Suppose I have this table [Table1]
假设我有这张表 [Table1]
Name Mark
------- ------
ABC 10
DEF 10
GHI 10
JKL 20
MNO 20
PQR 30
What should be my SQL statement to retrieve a record that looks like this: (group by [mark]). I have done the 1 and 2 columns but don't know how to accomplish the third column (concat the [name] with the same [mark])
我的 SQL 语句应该是什么来检索如下所示的记录：（按 [mark] 分组）。我已经完成了 1 和 2 列，但不知道如何完成第三列（将 [name] 与相同的 [mark] 连接起来）
mark count names
---- ----- -----------
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR
I'm using Microsoft SQL. Please help. Thanks
我正在使用 Microsoft SQL。请帮忙。谢谢
回答by Kirill Polishchuk
If MS SQL 2005 or higher.
如果 MS SQL 2005 或更高版本。
declare @t table([name] varchar(max), mark int)
insert @t values ('ABC', 10), ('DEF', 10), ('GHI', 10),
('JKL', 20), ('MNO', 20), ('PQR', 30)
select t.mark, COUNT(*) [count]
,STUFF((
select ',' + [name]
from @t t1
where t1.mark = t.mark
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t
group by t.mark
Output:
输出：
mark count values
----------- ----------- --------------
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR
回答by Jerry Nixon
Here's a performance-related answer!
这是一个与性能相关的答案！
http://jerrytech.blogspot.com/2010/04/tsql-concatenate-strings-1-2-3-and.html
http://jerrytech.blogspot.com/2010/04/tsql-concatenate-strings-1-2-3-and.html
Using XML functions in a large query is a performance killer.
在大型查询中使用 XML 函数是性能杀手。
Using a CTE is a performance superstar.
使用 CTE 是性能超级明星。
Check out the link, it will explain how.
查看链接，它将解释如何。
I admit the work to accomplish it is more.
我承认完成它的工作更多。
But the result is milliseconds over millions of rows.
但结果是数百万行的毫秒数。
回答by woggles
polishchuks solution is more elegant, but this is basically the same thing, we just deal with the trailing comma differently.
Polishchuks 解决方案更优雅，但这基本上是一样的，我们只是以不同的方式处理尾随逗号。
CREATE TABLE #Marks(Name nchar(3), Mark int)
INSERT INTO #Marks
SELECT 'ABC', 10 UNION ALL
SELECT 'DEF', 10 UNION ALL
SELECT 'GHI', 10 UNION ALL
SELECT 'JKL', 20 UNION ALL
SELECT 'MNO', 20 UNION ALL
SELECT 'PQR', 30
SELECT
mark,
[count],
CASE WHEN Len(Names) > 0 THEN LEFT(Names, LEN(Names) -1) ELSE '' END names
FROM
(
SELECT
Mark,
COUNT(Mark) AS [count],
(
SELECT DISTINCT
Name + ', '
FROM
#Marks M1
WHERE M1.Mark = M2.Mark
FOR XML PATH('')
) Names
FROM #Marks M2
GROUP BY Mark
) M
回答by David Stevenson
Loosely based on Itzik Ben-Gan, Inside Microsoft SQL Server 2005: T-SQL Programming, p. 215:
松散地基于 Itzik Ben-Gan，Inside Microsoft SQL Server 2005：T-SQL Programming，p。215：
IF OBJECT_ID('dbo.Table1') IS NOT NULL
DROP TABLE dbo.Table1 ;
GO
CREATE TABLE dbo.Table1 ( Name VARCHAR(10), Mark INT ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'ABC', 10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'DEF', 10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'GHI', 10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'JKL', 20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'MNO', 20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'PQR', 30 ) ;
WITH DelimitedNames AS
(
SELECT Mark, T2.Count,
( SELECT Name + ',' AS [text()]
FROM dbo.Table1 AS T1
WHERE T1.Mark = T2.Mark
ORDER BY T1.Mark
FOR XML PATH('')) AS Names
FROM ( SELECT Mark, COUNT(*) AS Count FROM dbo.Table1 GROUP BY Mark ) AS T2
)
SELECT Mark, Count, LEFT(Names, LEN(NAMES) - 1) AS Names
FROM DelimitedNames ;
