데이터베이스 질문과 답변
SELECT LEVEL as Depth,
LPAD(' ',2*(LEVEL-1))||a.MenuName as MenuName
FROM (
SELECT 1 as MenuNo, 0 as ParentNo, '회사' as MenuName FROM dual
UNION
SELECT 2,1, '기획처' FROM dual
UNION
SELECT 3,1, '관리처' FROM dual
UNION
SELECT 4,2, '기획1팀' FROM dual
UNION
SELECT 5,2, '기획2팀' FROM dual
UNION
SELECT 6,3, '예산지원팀' FROM dual
UNION
SELECT 7,3, '기업지원팀' FROM dual
)A
START WITH a.MenuNo = 1
CONNECT BY PRIOR a.MenuNo = a.ParentNo;
다음과 같이 결과가 나오는 데요..
| DEPTH | MENUNAME |
| 1 | 회사 |
| 2 | 기획처 |
| 3 | 기획1팀 |
| 3 | 기획2팀 |
| 2 | 관리처 |
| 3 | 예산지원팀 |
| 3 | 기업지원팀 |
이걸
| LV1 | LV2 | MENUNAME |
| 회사 | 기획처 | 기획1팀 |
| 회사 | 기획처 | 기획2팀 |
| 회사 | 관리처 | 예산지원팀 |
| 회사 | 관리처 | 기업지원팀 |
이렇게 칼럼으로 나타나게 하려면 어떻게 해야 할까요?
도움 부탁드립니다....
by 우리집아찌
[2018.03.30 14:58:42]
WITH T AS (
SELECT 1 as MenuNo, 0 as ParentNo, '회사' as MenuName FROM dual
UNION
SELECT 2,1, '기획처' FROM dual
UNION
SELECT 3,1, '관리처' FROM dual
UNION
SELECT 4,2, '기획1팀' FROM dual
UNION
SELECT 5,2, '기획2팀' FROM dual
UNION
SELECT 6,3, '예산지원팀' FROM dual
UNION
SELECT 7,3, '기업지원팀' FROM dual
)
SELECT REGEXP_SUBSTR( MenuName,'[^/]+',1,1) AS LV1
, REGEXP_SUBSTR( MenuName,'[^/]+',1,2) AS LV2
, REGEXP_SUBSTR( MenuName,'[^/]+',1,3) AS MenuName
FROM ( SELECT LEVEL AS DEPTH
,SYS_CONNECT_BY_PATH(MenuName, '/') AS MenuName
,CONNECT_BY_ISLEAF ISLEAF
FROM T A
WHERE CONNECT_BY_ISLEAF = 1
START WITH a.MenuNo = 1
CONNECT BY PRIOR a.MenuNo = a.ParentNo
)
by 동동동
[2018.04.02 09:16:33]
답변감사드립니다...
by 마농
[2018.03.30 14:59:17]
WITH t AS
(
SELECT 1 MenuNo, 0 ParentNo, '회사' MenuName FROM dual
UNION ALL SELECT 2,1, '기획처' FROM dual
UNION ALL SELECT 3,1, '관리처' FROM dual
UNION ALL SELECT 4,2, '기획1팀' FROM dual
UNION ALL SELECT 5,2, '기획2팀' FROM dual
UNION ALL SELECT 6,3, '예산지원팀' FROM dual
UNION ALL SELECT 7,3, '기업지원팀' FROM dual
)
SELECT REGEXP_SUBSTR(MenuName, '[^,]+', 1, 1) lv1
, REGEXP_SUBSTR(MenuName, '[^,]+', 1, 2) lv2
, REGEXP_SUBSTR(MenuName, '[^,]+', 1, 3) lv3
FROM (SELECT SYS_CONNECT_BY_PATH(MenuName, ',') AS MenuName
FROM t
WHERE CONNECT_BY_ISLEAF = 1
START WITH MenuNo = 1
CONNECT BY PRIOR MenuNo = ParentNo
)
;
by 동동동
[2018.04.02 09:24:16]
답변감사드립니다...^^
) 버튼을 클릭하여 작성 하시면 됩니다.