Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

User and many roles in string

Posted on 2011-09-03
3
Medium Priority
?
279 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 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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 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