id, pid 형태로 표현 (다시 올립니다) 0 4 80

by fly [SQL Query] [2018.06.19 11:59:34]


다시 질문 드립니다.

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
;

by 우리집아찌 [2018.06.19 13:14:54]
-- 수정했습니다.

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 
 

 


by fly [2018.06.19 16:25:01]

이렇게 간단한걸 제가 너무 어렵게 생각했었나봅니다.

감사합니다.


by 마농 [2018.06.19 13:21:42]

문의하신 형태는 원본에서 결과로 갈 수는 있으나.
다시 결과에서 원본으로 돌아갈 수 없는 구조입니다.
부모 자식 관계에서 보통은 한 부모 밑에 자식이 여렇인게 정상인데.
위 예에서는 한 자식 위에 부모가 여렇인 경우가 존재합니다.
예시가 잘못 된거 아닌지요?
예시가 맞다면? 위와 같은 구조변경으로 인해
결과물을 이용할 때 원치 않는 결과가 나올 수 도 있습니다.
예를 들면
원본에서 A 의 손자는 ZZ, 01 인데
결과에서 A 의 손자를 찾게 되면 ZZ, 01, AC 가 됩니다.


by fly [2018.06.19 16:26:44]

그렇군요. 제가 놓친 부분입니다. 세심한 답변에 감사드립니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입