GROUP BY to combine/concat a column

Question

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.

The function you need is called `group_concat` in MySQL. I don't know if there's an equivalent in SQLServer, you might check this question: [SQL group_concat function in SQL Server](http://stackoverflow.com/q/8868604/851811). — Xavi López, Mar 01 '13 at 09:44
Its not as simple as you might think - http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — RobJohnson, Mar 01 '13 at 09:45
Not hard either by SQL standards. Good reference @RobJohnson — samthebrand, Jun 07 '16 at 03:25

score 225 · Accepted Answer · edited Apr 11 '14 at 05:46

225

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

edited Apr 11 '14 at 05:46

Rashmi Pandit

23,230
17
71
111

answered Mar 01 '13 at 09:45

John Woo

258,903
69
498
492

26

Nice! To elaborate: you are using `STUFF()` to remove the first comma of the concatenated PageUrl string. The PageUrl string itself is created by using `FOR XML PATH()`, with an empty path, to concatenate the retrieved PageUrls. Pretty clever :) Some sources: `STUFF():` http://msdn.microsoft.com/en-us/library/ms188043.aspx `FOR XML:` https://www.simple-talk.com/sql/learn-sql-server/using-the-for-xml-clause-to-return-query-results-as-xml/ – Deruijter Mar 01 '13 at 10:06
14

This is an efficient answer to the user's question. However, it would be a much better answer if you would explain what is happening in the query and why exactly it works. – Eric J. Apr 07 '14 at 21:40
Added DISTINCT clause to avoid repeated page urls for the same user – Rashmi Pandit Apr 11 '14 at 05:47
2

Why is this query taking forever for me? Timing out. – samthebrand Jun 07 '16 at 03:59
Why not simply using `group_concat`? – Adam Sep 22 '16 at 12:36
1

@Adam this is `SQL Server` not `MySQL`. – John Woo Sep 27 '16 at 03:04
I can't get this to work for me, and I've only got two fields instead of four so I'm sure I've fracked up trying to translate this down. – Michael May 26 '21 at 23:48

score 11 · Answer 2 · edited Mar 26 '15 at 12:37

11

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

edited Mar 26 '15 at 12:37

Ullas

11,450
4
33
50

answered Mar 01 '13 at 12:01

Praveen Nambiar

4,852
1
22
31

4

You should add `AND T1.ACTIVITY = T2.ACTIVITY` in the inner query, otherwise you will get non correct result for some data. – Just a learner Mar 23 '16 at 10:12

GROUP BY to combine/concat a column

2 Answers2

Linked

Related