Group columns in query

Question

I have a query where I fetch the following columns:

ID       Name       Age       Hobby

ID, name and age comes from Table A Hobby comes from Table B

Example of results I can get is the following:

ID       Name       Age       Hobby
0        John       35        Fishing
0        John       35        Tennis
0        John       35        Hiking
1        Jane       31        Fishing
2        Nate       42        Fishing
2        Nate       42        Tennis

What I would like to have as result is the following instead:

ID       Name       Age       Hobby
0        John       35        Fishing, Tennis, Hiking
1        Jane       31        Fishing
2        Nate       42        Fishing, Tennis

Any ideas of how to achieve that?

Answer 1

Try this :

;WITH CTE AS(
    SELECT DISTINCT ID,NAME,AGE
    FROM TableName
)
SELECT *,
     STUFF(SELECT ','+ Hobby FROM TableName t1 WHERE t1.ID=CTE.ID FOR XML PATH(''),1,1,'')
FROM CTE