GROUP BY 组合/连接列

我有一张表格如下:

ID  User  Activity  PageURL
1  Me    act1      ab
2  Me    act1      cd
3  You   act2      xy
4  You   act2      st

我想按照用户和活动进行分组，这样我就可以得到这样的结果:

User  Activity  PageURL
Me    act1      ab, cd
You   act2      xy, st

如您所见，列 PageURL 被组合在一起，并以逗号分隔。

非常感谢你的指点和建议。

258141

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

小开

一个好问题。应该告诉你花了一些时间来破解这个问题。这是我的结果。

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