how to concatenate one to many records in sql server

sql, sql-server

Solution

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

Problem

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 ```

Original source