CODEWITHSUNDEEP
sqloracle-databasehierarchical-datarecursive-cte
Alexis Graham
Alexis Graham

Reputation: 1

Multiple column Tree Hierarchy (Manager, Employee) in Oracle Sql

I have an employees table as below :

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
103 Alexander Hunold AHUNOLD 590.423.4567 38720 IT_PROG 9000 - 102 60
104 Bruce Ernst BERNST 590.423.4568 39223 IT_PROG 6000 - 103 60
105 David Austin DAUSTIN 590.423.4569 38528 IT_PROG 4800 - 103 60
106 Valli Pataballa VPATABAL 590.423.4560 38753 IT_PROG 4800 - 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 39120 IT_PROG 4200 - 103 60
100 Steven King SKING 515.123.4567 37789 AD_PRES 24000 - - 90
101 Neena Kochhar NKOCHHAR 515.123.4568 38616 AD_VP 17000 - 100 90
102 Lex De Haan LDEHAAN 515.123.4569 36904 AD_VP 17000 - 100 90
108 Nancy Greenberg NGREENBE 515.124.4569 37485 FI_MGR 12008 - 101 100
109 Daniel Faviet DFAVIET 515.124.4169 37484 FI_ACCOUNT 9000 - 108 100
110 John Chen JCHEN 515.124.4269 38623 FI_ACCOUNT 8200 - 108 100
111 Ismael Sciarra ISCIARRA 515.124.4369 38625 FI_ACCOUNT 7700 - 108 100
112 Jose Manuel Urman JMURMAN 515.124.4469 38783 FI_ACCOUNT 7800 - 108 100
113 Luis Popp LPOPP 515.124.4567 39423 FI_ACCOUNT 6900 - 108 100

I need a hierarchal output where each column represents the child of the parent column on the left. Here's the desired expected result output.

Manager Id Emp 1 Emp 2 Emp 3
100 101 108 109
100 101 108 110
100 101 108 111
100 101 108 112
100 102 103 104
100 102 103 105
100 102 103 106
100 102 103 107

Here's the sample tree hierarchy (Manager - employee) diagram for reference.

manager emp tree

We can do it with multiple joins but is there a better way? Your help is really appreciated. Thanks!

We can do it with multiple joins but is there a better way?

with org_chart (
employee_id, first_name, last_name, manager_id, lvl)
as (
select employee_id, first_name, last_name, manager_id, 1 lvl
from   employees
where  manager_id is null
union  all
select  e.employee_id, e.first_name, e.last_name,  e.manager_id, oc.lvl + 1
from   org_chart oc
join   employees e
on     e.manager_id = oc.employee_id
 )
select distinct o.manager_id, o.employee_id, o.lvl, e.employee_id as emp2
from org_chart o
left join employees e
on o.employee_id = e.manager_id
where o.manager_id is not null
order by o.manager_id;

Upvotes: 0

Views: 95

Answers (1)

Bogdan Dincescu
Bogdan Dincescu

Reputation: 728

What about:

with org_chart (
 employee_id, first_name, last_name, manager_id, lvl, emp2)
as (
  select employee_id, first_name, last_name, manager_id, 1 lvl, cast(null as number) emp2
  from   employees
  where  manager_id is null
  union  all
  select  e.employee_id, e.first_name, e.last_name,  e.manager_id, oc.lvl + 1, e.manager_id
  from   org_chart oc
  join   employees e on  e.manager_id = oc.employee_id
)
select *
from org_chart

Upvotes: 0

Related Questions