CODEWITHSUNDEEP
sql-servert-sql
Ed Sinek
Ed Sinek

Reputation: 4977

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

Upvotes: 2

Views: 288

Answers (3)

Raymund
Raymund

Reputation: 7892

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

Upvotes: 3

Dan J
Dan J

Reputation: 16708

Since @OMG Ponies pointed out that my original "pivoting" response was off the mark for this question, let me defer to him for the solution. However, I'd still like to mention an alternative:

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).

If you're providing delimiters just so your presentation layer can (potentially) break the combined values back apart, why not just return them in a separate set, or just return the result of joining to your many-to-many table (leaving you with a bunch of duplicates), and just process the results in your presentation layer?

Two arguments for this:

  • String manipulation in something like C# is typically faster / less-painful than the same in SQL
  • Better separation of concerns - your data layer can simply return a set of roles-per-user, without concerning itself with how that data will be presented

Neither of those might be compelling, in your case, but I hope I've provided food for thought.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

SQL Server 2005+:

SELECT u.user_id,
       u.username,
       STUFF(ISNULL(SELECT ', ' + r.role_name
                      FROM USERSINROLES uir
                  JOIN ROLES r ON r.role_id = uir.role_id
                 WHERE uir.user_id = u.user_id
              GROUP BY r.role_name
                   FOR XML PATH ('')), ''), 1, 2, '')
  FROM USERS u

Upvotes: 2

Related Questions