How to concatenate multiple rows into one field in sql server

Question

Using simple query , I can do something like

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

and get:

shopping

fishing  

coding

but instead I just want 1 row, 1 col:

shopping, fishing, coding

for ref-- Can I concatenate multiple MySQL rows into one field?

I want to do this in sql server ??

Answer 1

SQL Server doesn't have great support for aggregate string concatenation. But you can do:

select stuff((select ', ' + hobbies
              from peoples_hobbies
              where person_id = 5
              for xml path ('')
             ), 1, 2, '') as hobbies;