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