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.