Hierarchical EMP data in Flat format

Oracle SQL Practical question Emp Hiearchical data in Flat format

-------------------------------------------------------------

select regexp_substr(EMPNO_LIST, '\w+', 1, 1) L1_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 1) L1_MGR,

   1 LV1,

   regexp_substr(EMPNO_LIST, '\w+', 1, 2) L2_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 2) L2_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 2) is not null then 2 end LV2,       

   regexp_substr(EMPNO_LIST, '\w+', 1, 3) L3_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 3) L3_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 3) is not null then 3 end LV3,

   regexp_substr(EMPNO_LIST, '\w+', 1, 4) L4_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 4) L4_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 4) is not null then 4 end LV4,

   regexp_substr(EMPNO_LIST, '\w+', 1, 5) L5_MGR,

   regexp_substr(ENAME_LIST, '\w+', 1, 5) L5_MGR,

   case when regexp_substr(EMPNO_LIST, '\w+', 1, 5) is not null then 5 end LV5                            

from (

select     trim(',' from sys_connect_by_path(ename,',')) ename_list,

   trim(',' from sys_connect_by_path(empno,',')) empno_list,

CONNECT_BY_ISLEAF ISLEAF

from emp

start with mgr is null

connect by prior empno=mgr)

where ISLEAF=1;

-------------------------------------------------------------

select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME,LV3, L4_EMPNO, L4_ENAME, LV4,

       l5.empno L5_EMPNO,l5.ename L5_ENAME,case when l5.empno is not null then 5 end LV5

    from (

    select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2, L3_EMPNO, L3_ENAME, LV3,

           l4.empno L4_EMPNO,l4.ename L4_ENAME,case when l4.empno is not null then 4 end LV4

    from (

        select L1_EMPNO, L1_ENAME, LV1, L2_EMPNO, L2_ENAME, LV2,

               l3.empno L3_EMPNO,l3.ename L3_ENAME,case when l3.empno is not null then 3 end LV3

        from (

            select L1_EMPNO, L1_ENAME, LV1,

                   l2.empno L2_EMPNO, l2.ename L2_ENAME, case when l2.empno is not null then 2 end LV2

            from (

                SELECT empno l1_empno,ename l1_ename,1 lv1

                FROM emp

                where mgr IS NULL) l1,

                emp l2

            where l2.mgr = l1.l1_empno) l2,

            emp l3

        where l3.mgr = l2.L2_EMPNO ) l3,

    emp l4

    where l4.mgr(+) = l3.L3_EMPNO) L4,

    emp l5

where l5.mgr(+) = l4.L4_EMPNO 

order by 1,4;

-------------------------------------------------------------