I'm trying to get a list of users from the asp.net membership table.
i want to display user id, username, email and role name in a single line.
My statement is as follows
SELECT [Dev].[dbo].[aspnet_Users].[UserId],
[UserName],
[Email],
[RoleName]
FROM [Dev].[dbo].[aspnet_Users]
INNER JOIN [Dev].[dbo].[aspnet_Membership]
ON [Dev].[dbo].[aspnet_Users].[UserId] = [Dev].[dbo].[aspnet_Membership].[UserId]
INNER JOIN [Dev].[dbo].[aspnet_UsersInRoles]
ON [Dev].[dbo].[aspnet_Users].[UserId] = [Dev].[dbo].[aspnet_UsersInRoles].[UserId]
Inner Join [Dev].[dbo].[aspnet_Roles]
On [Dev].[dbo].[aspnet_UsersInRoles].[RoleId] = [Dev].[dbo].[aspnet_Roles].[RoleId]
the results i get back are close to what i want, except that i am getting multiple rows back for the role names.
| UserID | UserName | Email | RoleName |
|--------|----------|-------|----------|
| 1 | Jon | Jon@ | Author |
| 1 | Jon | Jon@ | Approver |
| 1 | Jon | Jon@ | Admin |
| 2 | Mary | Mary@ | Author |
| 2 | Jon | Mary@ | Approver |
How do i get the multiple row names to display as a single line?
| UserID | UserName | Email | RoleName |
|--------|----------|-------|---------------------------|
| 1 | Jon | Jon@ | Author, Approver, Admin |
| 2 | mary | mary@ | Author, Approver |
