GROUP BY 组合/连接一列

2021-12-02 00:00:00 sql group-by sql-server-2008 sql-server

我有一个表格如下:

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

如您所见，PageURL 列组合在一起，并根据分组由逗号分隔.

As you can see, the column PageURL is combined together separated by a comma based on the group by.

非常感谢您的指点和建议.

Would really appreciate any pointers and advice.

推荐答案

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

  • SQLFiddle 演示

相关文章