GROUP BY to combine/concat a column

SqlSql ServerSql Server-2008Group By

Sql Problem Overview


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.

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

> 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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
Questionviv_aciousView Question on Stackoverflow
Solution 1 - SqlJohn WooView Answer on Stackoverflow
Solution 2 - SqlPraveen NambiarView Answer on Stackoverflow