Employee Heiracrchy Table Breakout

Toad for SQL Server
1

Hello,

I have a table that Employee ID and Reports To ID.

When I look at the data there are only 6 levels of managers.

I want to have a single table that shows Emplyoee ID, Level 1 Manager, Level 2 Manager, Level 3 Manager, Level 4 Manager, Level 5 Manager, Level 6 Manager.

Where the President is the only person in Level 1, his direct reports are in level 2, there direct reports are in level 3 and so forth down to 6.

I think I need to use a Lead/Lag statement and then use a decode to make the crosstab.

However, I am struggling on the Lead Lag code - I have the Decode done.

If I undersand Lead Lag my query would return

Current Table - Employe ID Column and Reports To ID Column

New Table -

Employee ID Reports To ID Lead/Lag Value - where the lead lag value is 1 through 6.

Can anyone assit me please

tbl name - Emp_Mgr_ID_List

Field Names - EMPID, RPTS_TO_ID

Thank you in advance,

galen

2

The model you are using is called an adjacency list. Joe Celko has written extensively on this. (www.simple-talk.com/…/article.aspx)

The general idea for you is to left outer join the table to itself multiple times (in your case 6) on emp_id = mgr_id. You’ll then have all the employees and their management chains in one place - you should then be able to put your select list together.

I haven’t quite got time to hack some code together - but if you post some DDL for sample data I’ll see what I can do…

3

Hello galen,

I prepared the following Recursive SQL CTE statement for fetching the hierarchy. For reaching the managers of an employee, I used new SQL LAG function

I hoped this helps for the solution

declare @lvl int = 5
declare @emp varchar(20) = ‘Level5 - 1’
;with cte as (
select h.EmployeID Employee, h.EmployeID, h.EmployeID ReportsToID, @lvl as Lvl
from Hierarchy h
where h.EmployeID NOT IN (SELECT ReportsToID from Hierarchy)
and EmployeID = @emp – IN (‘Level5 - 1’, ‘Level5 - 2’)-- @emp

union all

select cte.Employee, h.*, cte.Lvl - 1 as Lvl
from Hierarchy h
inner join cte on h.EmployeID = cte.ReportsToID

), manager as (
select
Lvl,
Employee,
Lag(ReportsToID,1,NULL) over (Order By Lvl) Lvl4Man,
Lag(ReportsToID,2,NULL) over (Order By Lvl) Lvl3Man,
Lag(ReportsToID,3,NULL) over (Order By Lvl) Lvl2Man,
Lag(ReportsToID,4,NULL) over (Order By Lvl) Lvl1Man
from cte
)
select * from manager where Lvl = @lvl

I could only provide 5 level of sample data, it is difficult without the DDL and sample data

Here is my data which I used for the above script

create table Hierarchy (EmployeID varchar(100), ReportsToID varchar(100))

insert into Hierarchy select ‘Level2 - 1’, ‘President’
insert into Hierarchy select ‘Level2 - 2’, ‘President’

insert into Hierarchy select ‘Level3 - 1’, ‘Level2 - 1’
insert into Hierarchy select ‘Level3 - 2’, ‘Level2 - 1’

insert into Hierarchy select ‘Level3 - 3’, ‘Level2 - 2’
insert into Hierarchy select ‘Level3 - 4’, ‘Level2 - 2’

insert into Hierarchy select ‘Level4 - 1’, ‘Level3 - 1’
insert into Hierarchy select ‘Level4 - 2’, ‘Level3 - 1’

insert into Hierarchy select ‘Level4 - 3’, ‘Level3 - 2’
insert into Hierarchy select ‘Level4 - 4’, ‘Level3 - 2’

insert into Hierarchy select ‘Level4 - 5’, ‘Level3 - 3’
insert into Hierarchy select ‘Level4 - 6’, ‘Level3 - 3’

insert into Hierarchy select ‘Level4 - 7’, ‘Level3 - 4’
insert into Hierarchy select ‘Level4 - 8’, ‘Level3 - 4’

insert into Hierarchy select ‘Level5 - 1’, ‘Level4 - 1’
insert into Hierarchy select ‘Level5 - 2’, ‘Level4 - 1’

insert into Hierarchy select ‘Level5 - 3’, ‘Level4 - 2’
insert into Hierarchy select ‘Level5 - 4’, ‘Level4 - 2’

insert into Hierarchy select ‘Level5 - 5’, ‘Level4 - 3’
insert into Hierarchy select ‘Level5 - 6’, ‘Level4 - 3’

insert into Hierarchy select ‘Level5 - 7’, ‘Level4 - 4’
insert into Hierarchy select ‘Level5 - 8’, ‘Level4 - 4’

insert into Hierarchy select ‘Level5 - 9’, ‘Level4 - 5’
insert into Hierarchy select ‘Level5 - 10’, ‘Level4 - 5’

insert into Hierarchy select ‘Level5 - 11’, ‘Level4 - 6’
insert into Hierarchy select ‘Level5 - 12’, ‘Level4 - 6’

insert into Hierarchy select ‘Level5 - 13’, ‘Level4 - 7’
insert into Hierarchy select ‘Level5 - 14’, ‘Level4 - 7’

insert into Hierarchy select ‘Level5 - 15’, ‘Level4 - 8’
insert into Hierarchy select ‘Level5 - 16’, ‘Level4 - 8’