By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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;
4 rows affected
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
| 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 |