Example on populating Employee Hierarchy

Scenario:
Employee Hierarchy (here max Hierarchy is consider to be 4) need to be populated as mentioned below:
Source:

Output:

Prerequisite:

create tables structure for temporary table.

PL/SQL code:

begin EXECUTE IMMEDIATE 'truncate table emp_hierarchy'; EXECUTE IMMEDIATE 'truncate table emp_hi'; FOR i IN (     SELECT         employee_id     FROM         hr.employees     ORDER BY         employee_id ) loop     EXECUTE IMMEDIATE 'drop table employees';     EXECUTE IMMEDIATE 'create table employees as select * from hr.employees where employee_id=' || i.employee_id;     EXECUTE IMMEDIATE 'truncate table emp_hi';     INSERT INTO emp_hi         WITH cte (             employee_id,             first_name,             manager_id,             pos         ) AS (             SELECT                 employee_id,                 first_name,                 manager_id,                 1 pos             FROM                 employees             UNION ALL             SELECT                 cte.employee_id,                 mgr.first_name,                 mgr.manager_id,                 cte.pos + 1 pos             FROM                 hr.employees mgr,                 cte             WHERE                 mgr.employee_id = cte.manager_id         )         SELECT             employee_id,             first_name,             5 - pos pos         FROM             cte;     COMMIT;     INSERT INTO emp_hierarchy         SELECT             employee_id,             mgr_level4,             coalesce(mgr_level3, mgr_level4) mgr_level3,             coalesce(mgr_level2, mgr_level3, mgr_level4) mgr_level2,             coalesce(mgr_level1, mgr_level2, mgr_level3, mgr_level4) mgr_level1         FROM             emp_hi PIVOT (                 MAX ( first_name )                 FOR pos                 IN ( 4 mgr_level4, 3 mgr_level3, 2 mgr_level2, 1 mgr_level1 )             );     commit;     end loop; END;

Output: