데이터베이스 질문과 답변
다시 질문 드립니다.
SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, 'ZZ' LV3, 'ZZ_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, '01' LV3, '01_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, NULL LV2, NULL LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL UNION ALL
SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, 'AF' LV3, 'AF_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'MS' LV2, 'MS_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'Z' LV2, 'Z_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL
;
위의 데이터를 아래와 같이 표현하고 싶습니다.
도움 부탁드립니다.
SELECT 'A' PID, 'A_NAME' PID_NAME, 'A1' ID, 'A1_NAME' ID_NAME FROM DUAL UNION ALL
SELECT 'A1' ID, 'A1_NAME' ID_NAME, 'ZZ' LV3, 'ZZ_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'A1' LV2, 'A1_NAME' LV2_NAME, '01' LV3, '01_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, NULL LV2, NULL LV2_NAME FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME FROM DUAL UNION ALL
SELECT 'A1' LV2, 'A1_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME FROM DUAL UNION ALL
SELECT 'AT' LV2, 'AT_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL UNION ALL
SELECT 'AT' LV2, 'AT_NAME' LV2_NAME, 'AF' LV3, 'AF_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'MS' LV2, 'MS_NAME' LV2_NAME FROM DUAL UNION ALL
SELECT 'MS' LV2, 'MS_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'Z' LV2, 'Z_NAME' LV2_NAME FROM DUAL UNION ALL
SELECT 'Z' LV2, 'Z_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL
;
-- 수정했습니다. WITH T1 AS ( SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, 'ZZ' LV3, 'ZZ_NAME' LV3_NAME FROM DUAL UNION ALL SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, '01' LV3, '01_NAME' LV3_NAME FROM DUAL UNION ALL SELECT 'B' LV1, 'B_NAME' LV1_NAME, NULL LV2, NULL LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL UNION ALL SELECT 'B' LV1, 'B_NAME' LV1_NAME, 'A1' LV2, 'A1_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL UNION ALL SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, 'AF' LV3, 'AF_NAME' LV3_NAME FROM DUAL UNION ALL SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'MS' LV2, 'MS_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME FROM DUAL UNION ALL SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'Z' LV2, 'Z_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME FROM DUAL ) SELECT * FROM ( SELECT LV1 PID , LV1_NAME PID_NAME , LV2 ID , LV2_NAME ID_NAME FROM T1 UNION ALL SELECT LV2 PID , LV2_NAME PID_NAME , LV3 ID , LV3_NAME ID_NAME FROM T1 ) WHERE PID IS NOT NULL GROUP BY PID , PID_NAME , ID , ID_NAME ORDER BY 1 , 3
이렇게 간단한걸 제가 너무 어렵게 생각했었나봅니다.
감사합니다.
문의하신 형태는 원본에서 결과로 갈 수는 있으나.
다시 결과에서 원본으로 돌아갈 수 없는 구조입니다.
부모 자식 관계에서 보통은 한 부모 밑에 자식이 여렇인게 정상인데.
위 예에서는 한 자식 위에 부모가 여렇인 경우가 존재합니다.
예시가 잘못 된거 아닌지요?
예시가 맞다면? 위와 같은 구조변경으로 인해
결과물을 이용할 때 원치 않는 결과가 나올 수 도 있습니다.
예를 들면
원본에서 A 의 손자는 ZZ, 01 인데
결과에서 A 의 손자를 찾게 되면 ZZ, 01, AC 가 됩니다.
그렇군요. 제가 놓친 부분입니다. 세심한 답변에 감사드립니다.
) 버튼을 클릭하여 작성 하시면 됩니다.