CODEWITHSUNDEEP
sql-servert-sql
DenStudent
DenStudent

Reputation: 928

SQL Server - List employee with manager and above

Let's say I have a dataset like this (I'm using employee and manager because it's a nice example):

EmployeeID | ManagerID | Role          | ...
1          | NULL      | CEO
2          | 1         | Manager
3          | 1         | Manager
5          | 2         | Team-Leader
6          | 2         | Team-Leader
7          | 3         | Team-Leader
8          | 2         | Employee
9          | 5         | Employee
10         | 6         | Employee
11         | 6         | Employee
12         | 7         | Employee

I would like to list the EmployeeID's with everyone that has a role above him. This is the result I'm aiming for:

EmployeeID | ManagerID
12         | 7
12         | 3
12         | 1
12         | NULL
11         | 6
11         | 2
11         | 1
11         | NULL
....
9          | 5
9          | 2
9          | 1
9          | NULL

So if I were to use a where on the EmployeeID in the result, I could get the employee and everyone above his ranks that he's responsible to.

I tried to work it out with a recursive cte, but that didn't seem to work out at all.

Any suggestions/ideas?

Upvotes: 0

Views: 351

Answers (1)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Try this:

with cte1(EmployeeID, ManagerID) as
(
select EmployeeID, max(ManagerID)
from Employees
group by EmployeeID
union all
select t2.EmployeeID, t1.ManagerID
from Employees t1
join cte1 t2 on t2.ManagerID = t1.EmployeeID
)
select * from cte1
order by EmployeeID desc, ManagerID desc

Upvotes: 2

Related Questions