how to concatenate one to many records in sql server

Question

I have two tables User and UserRoles in sql server. User table has basic user information e.g. UserId,Name etc and UserRoles has columns like UserId,RoleName. There is one to many relationship between these two tables i.e. one User can have multiple roles.

User

UserId  Name
1       A
2       B
3       C

UserRoles

UserId  Rolename
1       Manager
1       Event Organiser
2       Supervisor
2       Employee
2       Some otherRole

I need to write a query in sql which will return like following. i.e concatenate one to many records into a single string

UserId  Roles
1       Manager,Event Organiser
2       Supervisor,Employee,Some otherRole

Answer 1

You have to use Below 2 SQL Function

XML Path- For Concatenation

Stuff For Comma separation

select UserId,
    stuff((select ',' + t2.Rolename
     from UserRoles t2 where t1.UserId = t2.UserId
     for xml path('')),1,1,'') Roles
from UserRoles t1
group by UserId

SQL Fiddle