Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

User and many roles in string

Posted on 2011-09-03
3
271 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:johnkainn
  • 2
3 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36478171
In SQL Server 2008, you can use FOR XML to generate the comma-delimited list of roles. I will show an example if needed.
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36478200
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

0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 36478217
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.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question