아래와 같은 데이터를 (id, pid) 형태로 표현하고 싶습니다.
LV 값의 길이가 일정하지 않고 LV1, ,LV2, LV3의 값이 동일한 부분도 있지만 NAME은 각각 다릅니다.
id, pid 형태로 표현이 가능할까요?
SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A' LV2, 'A_NAME' LV2_NAME, 'ZZ' LV3, 'ZZ_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'A' LV1, 'A_NAME' LV1_NAME, 'A' LV2, 'A_NAME' LV2_NAME, '01' LV3, '01_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, 'A' LV2, 'A_NAME' LV2_NAME, 'AB' LV3, 'AB_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'B' LV1, 'B_NAME' LV1_NAME, 'A' LV2, 'A_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME, NULL DATA1, NULL DATA2, NULL DATA3 FROM DUAL UNION ALL
SELECT 'C' LV1, 'C_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, 'AF' LV3, 'AF_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'MS' LV2, 'MS_NAME' LV2_NAME, 'AC' LV3, 'AC_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'D' LV1, 'D_NAME' LV1_NAME, 'A' LV2, 'A_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME, NULL DATA1, NULL DATA2, NULL DATA3 FROM DUAL UNION ALL
SELECT 'E' LV1, 'E_NAME' LV1_NAME, 'AT' LV2, 'AT_NAME' LV2_NAME, NULL LV3, NULL LV3_NAME, NULL DATA1, NULL DATA2, NULL DATA3 FROM DUAL UNION ALL
SELECT 'E' LV1, 'E_NAME' LV1_NAME, '3' LV2, '3_NAME' LV2_NAME, 'ZC' LV3, 'ZC_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL UNION ALL
SELECT 'E' LV1, 'E_NAME' LV1_NAME, '3' LV2, '3_NAME' LV2_NAME, 'TC' LV3, 'TC_NAME' LV3_NAME, '데이터1' DATA1, '데이터2' DATA2, '데이터3' DATA3 FROM DUAL