?
Solved

User and many roles in string

Posted on 2011-09-03
3
Medium Priority
?
274 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
[X]
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
  • 2
3 Comments
 
LVL 60

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 60

Accepted Solution

by:
Kevin Cross earned 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

801 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