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

问题描述

我有一个表如下：

  ID用户活动PageURL 
 1我act1 ab 
 2我act1 cd 
 3您act2 xy 
 4您act2 st 
  

I希望按用户和活动进行分组，以便最终得到类似的结果：

 用户活动PageURL 
我act1 ab ，cd 
您act2 xy，st 
  

正如您所看到的，PageURL列合并

真的很感谢任何指针和建议。

解决方案

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

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.

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 Demo

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