DROP TABLE COC PURGE;

CREATE TABLE COC AS
WITH A AS (
SELECT
   EMPNO, JOB
 , CONNECT_BY_ROOT(SAL) S
 , CONNECT_BY_ROOT(EMPNO) N
 , CONNECT_BY_ROOT(DEPTNO) P
 , LEVEL LVL
FROM EMP CONNECT BY MGR = PRIOR EMPNO
), B AS (
SELECT EMPNO, JOB
 , S, N, P
 , MAX(LVL)OVER(PARTITION BY EMPNO) LVL
 , MAX(LVL)OVER(PARTITION BY EMPNO) - LVL D
FROM A
)
SELECT * FROM B
PIVOT(MAX(S) S, MAX(N) N, MAX(P) P FOR D IN (0 A,1 B,2 C,3 D))
;

MERGE INTO COC T USING (
WITH A AS (
SELECT
   EMPNO, JOB
 , CONNECT_BY_ROOT(SAL) S
 , CONNECT_BY_ROOT(EMPNO) N
 , CONNECT_BY_ROOT(DEPTNO) P
 , LEVEL LVL
FROM (
    SELECT TRUNC(EMPNO+DBMS_RANDOM.VALUE*3) EMPNO
     , SUBSTR(JOB,1,3) JOB
     , SAL+10 SAL
     , DEPTNO, MGR
    FROM EMP
   )
 CONNECT BY MGR = PRIOR EMPNO
), B AS (
SELECT EMPNO, JOB
 , S, N, P
 , MAX(LVL)OVER(PARTITION BY EMPNO) LVL
 , MAX(LVL)OVER(PARTITION BY EMPNO) - LVL D
FROM A
)
SELECT * FROM B
PIVOT(MAX(S) S, MAX(N) N, MAX(P) P FOR D IN (0 A,1 B,2 C,3 D))
) S
ON (T.EMPNO = S.EMPNO)
WHEN MATCHED THEN UPDATE
SET
   T.JOB = S.JOB
 , T.LVL = S.LVL
 , T.A_S = S.A_S
 , T.A_N = S.A_N
 , T.A_P = S.A_P
 , T.B_S = S.B_S
 , T.B_N = S.B_N
 , T.B_P = S.B_P
 , T.C_S = S.C_S
 , T.C_N = S.C_N
 , T.C_P = S.C_P
 , T.D_S = S.D_S
 , T.D_N = S.D_N
 , T.D_P = S.D_P
WHEN NOT MATCHED THEN INSERT (
   EMPNO, JOB, LVL
 , A_S, A_N, A_P
 , B_S, B_N, B_P
 , C_S, C_N, C_P
 , D_S, D_N, D_P
)
VALUES (
   S.EMPNO, S.JOB, S.LVL
 , S.A_S, S.A_N, S.A_P
 , S.B_S, S.B_N, S.B_P
 , S.C_S, S.C_N, S.C_P
 , S.D_S, S.D_N, S.D_P
)
;

COMMIT;

SET NUM 5

SELECT * FROM COC;