Skip to content
Advertisement

Oracle Hierarchical Query at depth level

I have a requirement to build a table from a hierarchical table. Table structure as below:

emp_hier table:

emp_id supervisorId
100 null
1 100
2 1
3 2

New table:

I have to write a select query on the emp_heir table and the selected data should look like this:

sel_emp_id rel_emp_id relation depth_lvl
100 100 self 0
100 1 My Repotee -1
100 2 My Repotee -2
100 3 My Repotee -3
1 100 My Mgr 1
1 1 self 0
1 2 My Repotee -1
1 3 My Repotee -2
2 1 My Mgr 1
2 2 self 0
2 3 My Repotee -1
3 100 My Mgr 3
3 1 My Mgr 2
3 2 My Mgr 1
3 3 self 0

Advertisement

Answer

You can use UNION ALL to combine a hierarchical query to get each row and its children to another hierarchical query to get all the ancestors:

SELECT CONNECT_BY_ROOT emp_id AS sel_emp_id,
       emp_id AS rel_emp_id,
       CASE LEVEL WHEN 1 THEN 'Self' ELSE 'My Reportee' END AS relation,
       1 - LEVEL AS depth_lvl
FROM   emp_hier
CONNECT BY PRIOR emp_id = supervisorid
UNION ALL
SELECT CONNECT_BY_ROOT emp_id,
       emp_id,
       'My Mgr',
       LEVEL - 1
FROM   emp_hier
WHERE  LEVEL > 1
CONNECT BY PRIOR supervisorid = emp_id
ORDER BY sel_emp_id, depth_lvl DESC

Which, for your sample data:

CREATE TABLE emp_hier (emp_id, supervisorId) AS
SELECT 100, null FROM DUAL UNION ALL
SELECT 1,   100  FROM DUAL UNION ALL
SELECT 2,   1    FROM DUAL UNION ALL
SELECT 3,   2    FROM DUAL;

Outputs:

SEL_EMP_ID REL_EMP_ID RELATION DEPTH_LVL
1 100 My Mgr 1
1 1 Self 0
1 2 My Reportee -1
1 3 My Reportee -2
2 100 My Mgr 2
2 1 My Mgr 1
2 2 Self 0
2 3 My Reportee -1
3 100 My Mgr 3
3 1 My Mgr 2
3 2 My Mgr 1
3 3 Self 0
100 100 Self 0
100 1 My Reportee -1
100 2 My Reportee -2
100 3 My Reportee -3

db<>fiddle here

Advertisement