GROUP BY 组合/连接一列 [英] GROUP BY to combine/concat a column

问题描述

我有一个表格如下:

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 演示

这篇关于GROUP BY 组合/连接一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！