[SOLVED] SQL: listing many-to-many from single query

SQL: listing many-to-many from single query

I've got 3 tables representing "Users", "Roles", and the many-to-many "UsersInRoles" - with keys: UserId, RoleId; pertinant columns: UserName, RoleName

In the admin html app, I want to show a list of all users and the roles they are in. From SQL, I'm trying to construct a single query that will return this information. The list of roles should be delimited so I can do the appropriate presentation manipulation (depending on presentation platform, like replace delimiter with BR tag).

Using one select for the list of users and then individual selects for each user to get the roles is straight-forward, but trying to construct a single select that outputs the below has got me stumped.

UserId  UserName  Roles
------  --------  -----
1       user1     Admin,Guest,PowerUser
2       user2     Guest
3       user3
4       user4     PowerUser,Guest

Thanks in advance

Solution

Heres your solution:

Converting / Parsing Rows to Delimited string column in SQL

EDIT

If you need further clarity here is the answer

WITH UserList as
(
SELECT UserID, UserName,
(SELECT 
RoleName + ',' AS 'data()'
FROM Roles
INNER JOIN 
UsersInRoles 
ON 
Roles.RoleID = UsersInRoles.RoleID
WHERE
UsersInRoles.UserID = Users.UserID FOR XML PATH('')) AS RoleCSV
FROM Users
)
SELECT UserID, UserName, LEFT(RoleCSV, LEN(RoleCSV)-1) as RoleCSV from UserList