sql concatenated roles

Question

I have a userdata table, a n to m relation between userdata and roles, and a roles table.

Example:

| Userdata   | UserRolesRel   | Roles        |
|------------|----------------|--------------|
| Id    Nick |Id_User Id_Role |  Id    Name  |
| 1     John |  1       1     |  1     Admin |
| 2     Frank|  1       2     |  2     User  |
|            |  1       3     |  3     Reader|
|            |  2       2     |              |

Now I would like to retrieve data like this

| Nick    |  Roles            |
|---------|-------------------|
| John    | Admin,User,Reader |
| Frank   | User              |

How could I acomplish it??

(I'm using sql server 2008)

score 2 · Answer 1 · edited May 23 '17 at 10:31

2

This is well covered in StackOverflow:

How to use GROUP BY to concatenate strings in SQL Server?

Concatenate many rows into a single text string?

edited May 23 '17 at 10:31

Community

1
1

answered Jul 08 '11 at 12:28

Jon Egerton

40,401
11
97
129

score 1 · Accepted Answer · answered Jul 08 '11 at 12:51

Test tables and data

declare @UserData table
(
  Id int,
  Nick varchar(10)
)

declare @UserRolesRel table
(
  Id_User int,
  Id_Role int
)

declare @Roles table
(
  Id int,
  Name varchar(10)
)

insert into @UserData values(1, 'John'),(2, 'Frank')
insert into @UserRolesRel values(1,1),(1,2),(1,3),(2,2)
insert into @Roles values(1, 'Admin'),(2, 'User'),(3, 'Reader')

Using the FOR XML PATH('') trick:

select UD.Nick,
       stuff((select ', '+R.Name
              from @Roles as R
                inner join @UserRolesRel as URR
                  on R.Id = URR.Id_Role
              where URR.Id_User = UD.Id
              for xml path(''), type).value('.', 'nvarchar(max)'), 1, 2, '') as Roles
from @UserData as UD

A bit longer version using recursive cte:

;with cte1 as
(
  select UD.Nick,
         R.Name as RoleName,
         URR.Id_User,
         URR.Id_Role,
         row_number() over(partition by URR.Id_User order by URR.Id_Role) as rn
  from @UserRolesRel as URR
    inner join @UserData as UD
      on URR.Id_User = UD.Id
    inner join @Roles as R
      on URR.Id_Role = R.Id  
),
cte2 as
(
  select C.Nick,
         cast(C.RoleName as varchar(max)) as Roles,
         C.Id_User,
         C.rn
  from cte1 as C
  where C.rn = 1
  union all    
  select C1.Nick,
         C2.Roles + ', '+C1.RoleName as Roles,
         C1.Id_User,
         C1.rn
  from cte1 as C1
    inner join cte2 as C2
      on C1.Id_User = C2.Id_User and
         C1.rn = C2.rn + 1
)

select C2.Nick, 
       C2.Roles
from cte2 as C2
  inner join (
               select Id_User, max(rn) as rn
               from cte1
               group by Id_User
             ) as M
    on M.rn = C2.rn and
       M.Id_User = C2.Id_User

score 0 · Answer 3 · answered Apr 27 '17 at 17:46

If it is >= SQL Server 2017, SQL Server Vnext, SQL Azure you can use STRING_AGG and group by as below

select u.Nick, [Roles] = string_agg(r.Name, ',') from @userdata u 
    inner join @UserRolesRel urr on u.id = urr.Id_User
    inner join @Roles r on urr.Id_Role = r.Id
    group by u.Nick

Your input tables:

declare @UserData table(Id int,Nick varchar(10))
declare @UserRolesRel table(Id_User int, Id_Role int)
declare @Roles table (Id int, Name varchar(10) )

insert into @UserData values(1, 'John'),(2, 'Frank')
insert into @UserRolesRel values(1,1),(1,2),(1,3),(2,2)
insert into @Roles values(1, 'Admin'),(2, 'User'),(3, 'Reader')

sql concatenated roles

3 Answers3