User and many roles in string

johnkainn
johnkainn used Ask the Experts™
on
I have 2 tables.  UserTable (UserId, UserName) and RoleTable (RoleId, UserId, Role).
I want to return all UserName and a string with all roles.
For example: Username='John Jonsson', Role='Administrator, Specialist'
How is best to do that?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kevin CrossChief Technology Officer
Most Valuable Expert 2011

Commented:
In SQL Server 2008, you can use FOR XML to generate the comma-delimited list of roles. I will show an example if needed.
Chief Technology Officer
Most Valuable Expert 2011
Commented:
Here is a self-contained example. Once you understand what it is doing and are satisfied with the results, you can remove the commented portion and reference your real tables.
/* EXAMPLE DATA ONLY */
;WITH UserTable(UserId, UserName) AS (
   SELECT 1, 'John Jonsson'
), RoleTable(RoleId, UserId, [Role]) AS (
   SELECT 1, 1, 'Administrator'
   UNION SELECT 2, 1, 'Specialist'
)
-- END EXAMPLE DATA 
-- SOLUTION STARTS BELOW
SELECT UserName
     , STUFF((SELECT ', '+[Role] 
              FROM RoleTable r
              WHERE r.UserId = u.UserId
              FOR XML PATH('')), 1, 2, '')
FROM UserTable u
;

Open in new window

Good question. It should be a very common requirement, thank for asking the question and therefore trigger me to do the research.  Let's give you the URL first:

Concatenating row values in Transact-SQL from Data management & exchange.

There URL seems to resolve your problem even in different scale. Look at the section "Concatenating values when the number of items are not known".

I am sure this is going to help me some day as well. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial