How to identify what are the employees has same Roles

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.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
chaauConnect With a Mentor Commented:
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
 
Easwaran ParamasivamAuthor Commented:
Brilliant!!!!
0
All Courses

From novice to tech pro — start learning today.