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.
by Pieter Brinkman
23. August 2009 03:04
With common table expressions you can save the results to a temporary result set and use this results set for other queries.
[code:tsql]
WITH temporaryNamedResultSet
AS
(
select UserName from aspnet_Users
)
select * from temporaryNamedResultSet
[/code]
Subscribe