CODEWITHSUNDEEP
sqlsql-server
rumi
rumi

Reputation: 3298

how to concatenate one to many records in sql server

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

Upvotes: 8

Views: 6181

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

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

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

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

Upvotes: 10

Related Questions