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

Here's a more modern way https://stackoverflow.com/questions/63236851/one-to-many-sql-select-concatenated-into-single-row — Zhang, Jul 11 '23 at 00:53

Prahalad Gaggar · Accepted Answer · 2013-12-18T11:05:54.903

10

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

edited Dec 18 '13 at 11:05

answered Dec 18 '13 at 11:00

Prahalad Gaggar

11,389
16
53
71

Luv, great answer - typo in Concatenation. Sorry I can't edit I don't have the rep for that small a change. – Liath Dec 18 '13 at 11:05

score 0 · Answer 2 · answered Dec 18 '13 at 11:19

try this,

Declare @t1 table(UserId int,name varchar(20))
insert into @t1 values(1,'A'),(2,'B'),(3,'C')
--select * from @t1
Declare @t table(UserId int,Rolename varchar(20))
insert into @t values(1,'Manager'),(1,'Event Organiser'),(2,'Supervisor'),(2,'Employee'),(2,'Some otherRole')

select 
userid,
stuff((select ','+ Rolename from @t b where a.UserId=b.UserId for xml path('')),1,1,'') [Roles]
 from @t1 a

how to concatenate one to many records in sql server

2 Answers2

Linked