Solved

User and many roles in string

Posted on 2011-09-03
3
259 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now