다시 질문 드립니다.
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