TSQL: Nested Select with multiple results to one (comma separated) string

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.



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


Don’t know if this is the best way, but it works.

Leave a Reply

Your email address will not be published.