Solved

How to identify what are the employees has same Roles

Posted on 2014-01-05
2
351 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
[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 Comments
 
LVL 25

Accepted Solution

by:
chaau earned 500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

752 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