Oracle Hierarchical Query at depth level

Question

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

Answer 1

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

Answer 2

Using CONNECT BY, you can connect all the employees and their relationships to each other. Then by joining that information together, you can print out the information in the format you desire.

WITH
    hier
    AS
        (    SELECT e.*, LEVEL AS lvl
               FROM emp_hier e
         CONNECT BY PRIOR emp_id = supervisorid
         START WITH supervisorid IS NULL)
  SELECT h1.emp_id          AS sel_emp_id,
         h2.emp_id          AS rel_emp_id,
         CASE
             WHEN h1.lvl - h2.lvl = 0 THEN 'self'
             WHEN h1.lvl - h2.lvl > 0 THEN 'My Mgr'
             ELSE 'My Reportee'
         END                AS relation,
         h1.lvl - h2.lvl    AS depth_level
    FROM hier h1, hier h2
ORDER BY CASE WHEN h1.supervisorid IS NULL THEN 0 ELSE 1 END, h1.emp_id, h1.lvl - h2.lvl DESC;



   SEL_EMP_ID    REL_EMP_ID       RELATION    DEPTH_LEVEL
_____________ _____________ ______________ ______________
          100           100 self                        0
          100             1 My Reportee                -1
          100             2 My Reportee                -2
          100             3 My Reportee                -3
            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

Answer 3

You can get the entire desired result with a single pass through the hierarchy (a single CONNECT BY query), with no self-join and no union all.

Instead, I use a helper inline view (with just one row and two numeric columns, with the values -1 and 1); since each "relationship" appears exactly twice in the output, with the exception of "Self", I use this to do an ad-hoc duplication of the rows from the hierarchical query.

I used the table in MT0's post for testing. I don't show the result - it's the same (just ordered differently).

with
  h (x) as (select  1 from dual union all select -1 from dual)
, p (ancestor, emp, depth) as (
    select  connect_by_root(emp_id), emp_id, level - 1
    from    emp_hier
    connect by supervisorid = prior emp_id
  )
select case h.x when 1 then emp      else ancestor end as emp_self,
       case h.x when 1 then ancestor else emp      end as emp_related,
       case when h.x      = 1 then 'Mgr'
            when p.depth != 0 then 'Reportee'
                              else 'Self'     end      as rel,
       h.x * p.depth                                   as depth_level
from   p join h on h.x = -1 or p.depth != 0   -- do not duplicate "Self"
order  by emp_self, depth_level desc, emp_related -- or whatever (if/as needed)
;