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.