Use SQL to Find Values Where its Many to One

Question

The first part of my problem is not knowing how to correctly ask and phrase this question, so I apologize if this is a duplicate.

Aside from a users table, I have a table like this

[UserRoles]
UserID | RoleName
-------|---------
1      | Role1
1      | Role2
1      | Role3
1      | Role4
2      | Role1
2      | Role4

Another table like this:

[RoleRoleTypes]
RoleName | Function
-------------------
Role1    | FunctionA
Role2    | FunctionA
Role3    | FunctionB
Role4    | FunctionC

What I want to end up with is a table with the UserID, a comma separated list of roles, a comma separated list of functions.

End Result

UserId | Roles                       | Functions
-------|---------------------------- |------------------------------------
1      | Role1, Role2, Role3, Role4  | FunctionA, FunctionB, FunctionC 
2      | Role1, Role4                | FunctionC

The first two columns are rather easy, using stuff to create the comma seperated list of roles. But I am unsure how to go about calculating a list of functions given that multiple roles make up a function. A user must have all the roles listed to have the function. So if you have role1, but not role 2, you don't have FunctionA. I tried a variety of joins but never got anywhere close to the correct data. Any advice?

Here is what I have for the first two columns

SELECT u.email, Roles = STUFF(
    (SELECT ',' + RoleName
    From UserRoles ur
    WHERE ur.UserID = u.UserID
    FOR XML PATH('')), 1, 1, '')
FROM Users u

EDIT Added clarification and end result example

What do you mean "multiple rows make up a function". If a user had Role1 but NOT Role2, would they, or would they not, have FunctionA? — Tab Alleman, Sep 21 '17 at 20:02
Good question, they would need all the roles to make up a function. — Taylor Ackley, Sep 21 '17 at 20:10
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — Err, Sep 21 '17 at 21:48

Radim Bača · Accepted Answer · 2017-09-21T21:18:33.223

2

I guess you just need to use similar approach as in the case of RoleNames and use distinct to avoid duplicate functions

SELECT u.userid, 
   Roles = STUFF(
     (SELECT ', ' + RoleName
      FROM UserRoles ur
      WHERE ur.UserID = u.UserID
      FOR XML PATH('')
     ), 1, 1, ''), 
  Functions = STUFF(
     (SELECT distinct ', ' + rt.[function]
      FROM [UserRoles] ur
      JOIN [RoleRoleTypes] rt ON ur.RoleName = rt.RoleName 
      WHERE ur.UserID = u.UserID
      FOR XML PATH('')
     ), 1, 1, '')
FROM [User] u

demo

edited Sep 21 '17 at 21:18

answered Sep 21 '17 at 19:27

Radim Bača

10,646
1
19
33

This, unfortunately, did not work :( The distinct keyword seemed to have no effect on the result. – Taylor Ackley Sep 21 '17 at 20:43
@TaylorAckley I have updated the answer and created a demo. I feel that should be a correct answer. Please point out the demo inaccuracy if there is any. – Radim Bača Sep 21 '17 at 21:21

score 0 · Answer 2 · answered Sep 22 '17 at 11:25

0

You are required to use the Distinct keyword in your query to avoid duplicacy of results.

answered Sep 22 '17 at 11:25

Zahid Khan

2,130
2
18
31

Use SQL to Find Values Where its Many to One

2 Answers2