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

x
?
Solved

How to identify what are the employees has same Roles

Posted on 2014-01-05
2
Medium Priority
?
359 Views
Last Modified: 2014-01-05
I've table as given below. One employee can be assigned to multiple roles. I would like to identify what users has the same set of roles.

Ex

EmpID RoleID

1           2
1           3
4           2
4           3
5           1

I above example, Employees 1 and  4 has same set of roles. Hence I would like to get the result as (1,4).

How to achieve this using query. Please do assist.
0
Comment
Question by:Easwaran Paramasivam
2 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39758415
You need to use FOR XML PATH, like this:
;WITH r AS (
SELECT EmpID, 
    (STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR(100),P1.RoleID) AS [text()]
            FROM Table1 P1
            WHERE P1.EmpID = P.EmpID 
            FOR XML PATH('')), 1, 1, '')) AS Roles
FROM Table1 P
GROUP BY EmpID)
SELECT Roles, 
    (STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR(100),r1.EmpID) AS [text()]
            FROM r r1
            WHERE r1.Roles = r.Roles 
            FOR XML PATH('')), 1, 1, '')) AS Empls
FROM r
GROUP BY Roles
HAVING COUNT(*) > 1 -- comment this line if you want to include the sets with employees for whom no matching pairs found

Open in new window

SQL Fiddle
0
 
LVL 16

Author Closing Comment

by:Easwaran Paramasivam
ID: 39758427
Brilliant!!!!
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
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.
Integration Management Part 2
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…

773 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