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

Question

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;

Make it easy to assist you, provide sample table data and the expected result as properly formatted text (i.e. no links, no images.) — jarlh
– jarlh, Commented Aug 8, 2023 at 9:41
Hi - as you (I assume it was you?) tagged this question with "recursive-cte" I assume you know about recursive queries for querying hierarchical data - so you seem to already know the answer to your question? — NickW
– NickW, Commented Aug 8, 2023 at 10:23
Hi Nick, After some research I think recursive CTE could be used but I'm still not sure how to do it to get desired results, that's why I posted it here. — Alexis Graham
– Alexis Graham, Commented Aug 8, 2023 at 11:29
The old Oracle way (since 7.x if I do recall well) is with connect by prior e.employee_id = e.manager_id start with manager_id is null. Can't say whether it performs better or not. — Bogdan Dincescu
– Bogdan Dincescu, Commented Aug 8, 2023 at 11:29
@BogdanDincescu the only limitation I'm facing with this method is that I need to add new columns for each node, should I use case statements for that? — Alexis Graham
– Alexis Graham, Commented Aug 8, 2023 at 11:39

Bogdan Dincescu · Accepted Answer · 2023-08-08 11:53:49Z

0

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

answered Aug 8, 2023 at 11:53

Bogdan Dincescu

7282 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexis Graham Over a year ago

no, this doesn't get desired result. I mean the column emp2 is coming out not as expected

Guillaume Outters · Accepted Answer · 2025-06-17 12:45:12Z

Top to bottom

You will have to build and store the "path" to reach each employee during the recursive CTE (note that I only added one column to your query):

with org_chart (
employee_id, first_name, last_name, manager_id, lvl, hierarchy)
as (
select employee_id, first_name, last_name, manager_id, 1 lvl, cast(employee_id as varchar2(4000)) hierarchy 
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, oc.hierarchy||' / '||e.employee_id
from   org_chart oc
join   employees e
on     e.manager_id = oc.employee_id
 )
select *
from org_chart o
-- Not needing to display managers with employees? Then filter them out:
where not exists (select 1 from org_chart o2 where o2.hierarchy like o.hierarchy||' / %')
order by hierarchy;

EMPLOYEE_ID	FIRST_NAME	LAST_NAME	MANAGER_ID	LVL	HIERARCHY
109	Daniel	Faviet	108	4	100 / 101 / 108 / 109
110	John	Chen	108	4	100 / 101 / 108 / 110
111	Ismael	Sciarra	108	4	100 / 101 / 108 / 111
112	Jose Manuel	Urman	108	4	100 / 101 / 108 / 112
113	Luis	Popp	108	4	100 / 101 / 108 / 113
104	Bruce	Ernst	103	4	100 / 102 / 103 / 104
105	David	Austin	103	4	100 / 102 / 103 / 105
106	Valli	Pataballa	103	4	100 / 102 / 103 / 106
107	Diana	Lorentz	103	4	100 / 102 / 103 / 107

(see first query of this fiddle)

If you want to split this into columns, you can use substr and instr (after having added /s before and after, to avoid coalesce(nullif(instr(…), 0), length(…))):

select employee_id, first_name, last_name, manager_id, 1 lvl, '/'||employee_id||'/' hierarchy […]
union  all
select  e.employee_id, e.first_name, e.last_name,  e.manager_id, oc.lvl + 1, oc.hierarchy||e.employee_id||'/' […]
[…]
  substr(hierarchy, nullif(instr(hierarchy, '/', 1, 1), 0) + 1, instr(hierarchy, '/', 1, 2) - instr(hierarchy, '/', 1, 1) - 1) "Emp 1",
  substr(hierarchy, nullif(instr(hierarchy, '/', 1, 2), 0) + 1, instr(hierarchy, '/', 1, 3) - instr(hierarchy, '/', 1, 2) - 1) "Emp 2",
  […]

See the second query of the fiddle.

Bottom to top

You can go bottom to top, too; and this time, to change a bit, we'll use pivot to generate the "Emp x" columns.

with ape (employee_id, lvl, hier_id) as ( -- All Path Elements.
  select employee_id, 0, employee_id from employees
  union all
  select ape.employee_id, lvl + 1, e.manager_id
  from ape join employees e on e.employee_id = ape.hier_id
  where e.manager_id is not null
),
api as ( -- All Path Inverted
  select
    employee_id, hier_id,
    -- We went from bottom to top, so our level is inverted. Put it back in the right order.
    -- As we will want it to become a column name, concatenate it here.
    'Emp '||(max(lvl) over (partition by employee_id) - lvl) lvl
  from ape
)
select employee_id, p.*
from api
pivot
(
  max(hier_id)
  for lvl in ('Emp 0' as "Manager Id", 'Emp 1', 'Emp 2', 'Emp 3')
) p;

EMPLOYEE_ID	Manager Id	'Emp 1'	'Emp 2'	'Emp 3'
104	100	102	103	104
105	100	102	103	105
…

(third query of the fiddle)

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Top to bottom

Bottom to top

Comments

Your Answer

Hot Network Questions

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

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

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Top to bottom

Bottom to top

Comments

Your Answer

Sign up or log in

Post as a guest

Related

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