SQL GROUP BY 组合/连接一列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15154644/
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
GROUP BY to combine/concat a column
提问by viv_acious
I have a table as follow:
我有一个表如下：
ID User Activity PageURL
1 Me act1 ab
2 Me act1 cd
3 You act2 xy
4 You act2 st
I want to group by User and Activity such that I end up with something like:
我想按用户和活动分组，这样我最终会得到类似的结果：
User Activity PageURL
Me act1 ab, cd
You act2 xy, st
As you can see, the column PageURL is combined together separated by a comma based on the group by.
如您所见，PageURL 列组合在一起，以基于分组方式的逗号分隔。
Would really appreciate any pointers and advice.
非常感谢任何指示和建议。
回答by John Woo
SELECT
[User], Activity,
STUFF(
(SELECT DISTINCT ',' + PageURL
FROM TableName
WHERE [User] = a.[User] AND Activity = a.Activity
FOR XML PATH (''))
, 1, 1, '') AS URLList
FROM TableName AS a
GROUP BY [User], Activity
回答by Praveen Nambiar
A good question. Should tell you it took some time to crack this one. Here is my result.
一个好问题。应该告诉你破解这个需要一些时间。这是我的结果。
DECLARE @TABLE TABLE
(
ID INT,
USERS VARCHAR(10),
ACTIVITY VARCHAR(10),
PAGEURL VARCHAR(10)
)
INSERT INTO @TABLE
VALUES (1, 'Me', 'act1', 'ab'),
(2, 'Me', 'act1', 'cd'),
(3, 'You', 'act2', 'xy'),
(4, 'You', 'act2', 'st')
SELECT T1.USERS, T1.ACTIVITY,
STUFF(
(
SELECT ',' + T2.PAGEURL
FROM @TABLE T2
WHERE T1.USERS = T2.USERS
FOR XML PATH ('')
),1,1,'')
FROM @TABLE T1
GROUP BY T1.USERS, T1.ACTIVITY
