SQL Query for aggregation/concatenation

Question

I have a table like this:

ID    Name
----------
1   john
1   molly
2   greg
2   sean
1   holly
2   mill

What should the SQL Query be to aggregate results like the following:

ID  Name
-------------
1   john/molly/holly
2   greg/sean/mill

Joe Stefanelli · Accepted Answer · 2011-10-25 21:55:43Z

4

Note: The STUFF function simply removes the leading / from the string returned.

SELECT t1.id, 
       STUFF((SELECT '/' + t2.name
                FROM YourTable t2
                WHERE t1.id = t2.id
                ORDER BY t2.name
                FOR XML PATH('')),1,1,'') AS Name
    FROM YourTable t1
    GROUP BY t1.id

edited Oct 25, 2011 at 21:55

answered Oct 25, 2011 at 21:49

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query for aggregation/concatenation

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related