WITH A AS (SELECT '1' AS COL, '물' AS NAME, '' AS UP_COL FROM DUAL UNION ALL SELECT '2' AS COL, '뿌리' AS NAME, '1' AS UP_COL FROM DUAL UNION ALL SELECT '3' AS COL, '나무' AS NAME, '2' AS UP_COL FROM DUAL UNION ALL SELECT '4' AS COL, '나뭇잎' AS NAME, '3' AS UP_COL FROM DUAL UNION ALL SELECT 'A' AS COL, '나무' AS NAME, '' AS UP_COL FROM DUAL UNION ALL SELECT 'B' AS COL, '종이' AS NAME, 'A' AS UP_COL FROM DUAL UNION ALL SELECT 'C' AS COL, '공책' AS NAME, 'B' AS UP_COL FROM DUAL UNION ALL SELECT 'D' AS COL, '서점' AS NAME, 'C' AS UP_COL FROM DUAL ) SELECT COL, NAME , CONNECT_BY_ROOT COL AS COL_1 , CONNECT_BY_ROOT NAME AS NAME_1 --?? AS COL_2 --?? AS NAME_2 --?? AS COL_3 --?? AS NAME_3 , DECODE(CONNECT_BY_ISLEAF, 1, COL) AS COL_4 , DECODE(CONNECT_BY_ISLEAF, 1, NAME) AS NAME_4 FROM A START WITH UP_COL IS NULL CONNECT BY PRIOR COL = UP_COL ;
원하는 결과
- 각 레코드별로 레벨별 값을 가지고 있고 싶은데요.
CONNECT_BY_ROOT, CONNECT_BY_ISLEAF를 이용해서 첫번째와 마지막 LEVEL의 값은 구했는데요.
두번째, 세번째 레벨에 대한 값을 어떻게 얻을 수 있을까요?
-------------------------------------------------------------------------
COL1 NAME COL_1 NAME_1 COL_2 NAME_2 ........3,...4
-------------------------------------------------------------------------
1 물 1 물
2 뿌리 1 물 2 뿌리
3 나무 1 물 2 뿌리
4 나뭇잎 1 물 2 뿌리
A 나무 A 나무
B 종이 A 나무 B 종이
C 공책 A 나무 B 종이
D 서점 A 나무 B 종이
WITH A AS (SELECT '1' AS COL, '물' AS NAME, '' AS UP_COL FROM DUAL UNION ALL SELECT '2' AS COL, '뿌리' AS NAME, '1' AS UP_COL FROM DUAL UNION ALL SELECT '3' AS COL, '나무' AS NAME, '2' AS UP_COL FROM DUAL UNION ALL SELECT '4' AS COL, '나뭇잎' AS NAME, '3' AS UP_COL FROM DUAL UNION ALL SELECT 'A' AS COL, '나무' AS NAME, '' AS UP_COL FROM DUAL UNION ALL SELECT 'B' AS COL, '종이' AS NAME, 'A' AS UP_COL FROM DUAL UNION ALL SELECT 'C' AS COL, '공책' AS NAME, 'B' AS UP_COL FROM DUAL UNION ALL SELECT 'D' AS COL, '서점' AS NAME, 'C' AS UP_COL FROM DUAL ) SELECT COL, NAME , CONNECT_BY_ROOT COL AS COL_1 , CONNECT_BY_ROOT NAME AS NAME_1 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,2) COL_2 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,2) NAME_2 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,3) COL_3 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,3) NAME_3 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (COL,'^'),'[^^]+',1,4) COL_4 , REGEXP_SUBSTR(SYS_CONNECT_BY_PATH (NAME,'^'),'[^^]+',1,4) NAME_4 FROM A START WITH UP_COL IS NULL CONNECT BY PRIOR COL = UP_COL