Separate columns using connect by

Question

I have a table which has rows in a parent child relationship. The maximum parent-child relationship depth is 3.

One parent would have only one child.

Table definition -

CREATE TABLE USERS {
    empid NUMBER,
    name VARCHAR2,
    department VARCHAR2(20),
    oldempid NUMBER,
}

Data -

EMPID NAME DEPARTMENT OLDEMPID
    1  FOO         IT        2
    2 null         HR        4
    3  BAR      ADMIN     null
    4 null     DESIGN     null

Desired output -

EMPID NAME DEPARTMENT_1 DEPARTMENT_2 DEPARTMENT_3
    1  FOO           IT           HR       DESIGN
    3  BAR        ADMIN         null         null

Full Desired output -

EMPID NAME DEPARTMENT_1 DEPARTMENT_2 DEPARTMENT_3 OLDEMPID_1 OLDEMPID_2
    1  FOO           IT           HR       DESIGN          2          4
    3  BAR        ADMIN         null         null       null       null

I have tried using CONNECT BY PRIOR

SELECT CONNECT_BY_ROOT EMPID,
       NAME,
         RTRIM(LTRIM(SYS_CONNECT_BY_PATH(DEPARTMENT, '-'), '-'), '-') AS DEPARTMENT,
         RTRIM(LTRIM(SYS_CONNECT_BY_PATH(OLDEMPID, '-'), '-'), '-') AS OLDEMPID
   FROM USERS
   WHERE CONNECT_BY_ISLEAF = 1
   CONNECT BY PRIOR OLDEMPID = EMPID;

The output I get is -

EMPID   NAME    DEPARTMENT     OLDEMPID
    1    null   IT-HR-DESIGN   1-2-4   
    3    BAR    ADMIN              null

Answer 1

You also need to pivot your results. You can do that by hand, or you can use the PIVOT operation (since you tagged this with oracle11g - the first version in which PIVOT became available). The subquery (in the FROM clause towards the end) is the hierarchical query with "connect by."

with
     users ( EMPID, NAME, DEPARTMENT, OLDEMPID ) as (
       select 1, 'FOO', 'IT'    ,    2 from dual union all
       select 2, null , 'HR'    ,    4 from dual union all
       select 3, 'BAR', 'ADMIN' , null from dual union all
       select 4, null,  'DESIGN', null from dual
     ),
     tops ( empid ) as ( 
       select empid    from users minus 
       select oldempid from users
     )
select cbr_empid as empid, cbr_name as name,
       "1_DEPARTMENT" as department_1, "2_DEPARTMENT" as department_2, 
       "3_DEPARTMENT" as department_3, "2_EMPID" as oldempid_2, "3_EMPID" as oldempid_3
from (
       select empid, department, level as lvl, 
              connect_by_root(empid) as cbr_empid, connect_by_root(name) as cbr_name
       from   users
       connect by prior oldempid = empid
       start with empid in (select empid from tops)
     )
pivot ( max(department) as department, max(empid) as empid for lvl in (1, 2, 3))
;

OUTPUT:

     EMPID NAME DEPARTMENT_1 DEPARTMENT_2 DEPARTMENT_3 OLDEMPID_2 OLDEMPID_3
---------- ---- ------------ ------------ ------------ ---------- ----------
         1 FOO  IT           HR           DESIGN                2          4
         3 BAR  ADMIN