by Pieter Brinkman
25. August 2009 03:22
For this example I will use the default asp.net Membership tables.
Let say: I need to return all users with their roles (comma separated) in one query. After a long time Googling I found the following solution.
[code:tsql]
select
UserName,
(select roles.RoleName + ', '
FROM aspnet_Roles roles
join aspnet_UsersInRoles usersInRole on roles.RoleId = usersInRole.RoleId
WHERE usersInRole.UserId = aspUser.UserId
for xml path('')) as roles
from
aspnet_Users aspUser
[/code]
Don't know if this is the best way, but it works.
Subscribe