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 u开发者_如何转开发ser 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,

--Ed

--EDIT--

now working with the following query (thanks to all, esp. Raymund & his blog):

WITH RolesList AS
(
  SELECT u.UserName,
  (
    SELECT r.RoleName + ',' AS 'data()'
    FROM Roles r
    JOIN UsersInRoles ur ON ur.RoleId = r.RoleId
    WHERE ur.UserId = u.UserId FOR XML PATH('')
  ) AS RolesCSV
  FROM Users u
) SELECT UserName, LEFT(RolesCSV, LEN(RolesCSV)-1) AS RolesCSV FROM RolesList

---

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

---

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

---

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.