How To Write a 'GROUP BY' query to get all the Roles assigned to User for a given applicatoin

Question

I have two tables User and User_Roles. A User can have multiple Roles.

USER TABLE

User_Id  User_Name
1        Edward  
2        John 
3        Mark  

USER_ROLES TABLE

User_ID    Role
1          Admin
1          Writer
1          Form
2          Writer
3          Form
3          Writer

I want to write a query that gives me the following result

User_ID    User_Name   Role
1          Edward      Admin, Writer,Form
2          John        Writer
3          Mark        Form,Writer

I have tried using a GROUP BY and I know this is how I can get the result but I am just not doing it right.

SELECT COUNT(T0.[User_Id]),T0.[User_Name],T1.[Role]      
FROM  USER T0  
INNER JOIN  USER_ROLES T1 ON T0.User_ID = T1.User_ID  
GROUP BY  T0.[User_Name], T1.[Role]

I am using a COUNT for test purpose only because when I do a GROUP BY with an aggregate function , I get an error.

Answer 1

Use aggregation and STRING_AGG() (available starting SQL Server 2017):

SELECT u.User_ID, u.User_Name, STRING_AGG(r.Role, ',') AS Roles
FROM  user u  
INNER JOIN  user_roles r ON r.User_Id = u.User_Id
GROUP BY u.User_ID, u.User_Name

NB: you might want to order the aggregated strings so you do get predictable values, like:

STRING_AGG(r.Role, ',') WITHIN GROUP (ORDER BY r.Role) AS Roles

Answer 2

An alternative is to us the FOR XML / STUFF trick :

  SELECT 
   U.USER_NAME,
   STUFF((SELECT ',' + UR.ROLE 
          FROM [USER_ROLES] UR
          WHERE UR.USER_ID = U.USER_ID
          FOR XML PATH('')), 1, 1, '') [ROLES]
FROM [USER] U
GROUP BY U.USER_NAME, U.USER_ID
ORDER BY 1

https://rextester.com/YLIJQ30828