PIVOT, UNPIVOT, 테이블 별칭

 

피벗을 써서 데이터를 뽑았는데  문득 의문이 들었음.. 열이랑 행이랑 컬럼바꾸고 싶다고

그래서 어케해야하나~하다가 UNPIVOT을 알게됨
https://gent.tistory.com/382  >> 참고사이트

---

 

여기서 또 알게된 점이 

 

테이블을 별칭으로 감싸서 출력이 가능하더라..

SELECT * FROM UNPIVOTED_DATA하면 UNPIVOT한 데이터가 나옴 

 

 

이런식으로 쪼개지는데 

 

WITH ALL_DUTY_CD AS (
    SELECT '0011' AS NOW_DUTY_CD FROM dual
    UNION SELECT '0012' FROM dual
    UNION SELECT '0013' FROM dual
    UNION SELECT '0014' FROM dual
    UNION SELECT '0015' FROM dual
    UNION SELECT '0016' FROM dual
    UNION SELECT '0017' FROM dual
    UNION SELECT '0018' FROM dual
    UNION SELECT '0019' FROM dual
),
PIVOTED_DATA AS (
    SELECT 
        (SELECT CD_NM FROM ${HUMETRO}.TB_CMCD WHERE UP_CD = 'GA02' AND CD = B.NOW_DUTY_CD) AS CD_NM,
        COUNT(CASE WHEN A.DUTY_CD = '0011' THEN 1 END) AS CD11,
        COUNT(CASE WHEN A.DUTY_CD = '0012' THEN 1 END) AS CD12,
        COUNT(CASE WHEN A.DUTY_CD = '0013' THEN 1 END) AS CD13,
        COUNT(CASE WHEN A.DUTY_CD = '0014' THEN 1 END) AS CD14,
        COUNT(CASE WHEN A.DUTY_CD = '0015' THEN 1 END) AS CD15,
        COUNT(CASE WHEN A.DUTY_CD = '0016' THEN 1 END) AS CD16,
        COUNT(CASE WHEN A.DUTY_CD = '0017' THEN 1 END) AS CD17,
        COUNT(CASE WHEN A.DUTY_CD = '0018' THEN 1 END) AS CD18,
        COUNT(CASE WHEN A.DUTY_CD = '0019' THEN 1 END) AS CD19,
        COUNT(A.NOW_DUTY_CD) AS TOTAL
    FROM 
        ALL_DUTY_CD B
    LEFT JOIN 
        TB_JBCHG A ON B.NOW_DUTY_CD = A.NOW_DUTY_CD
    AND 
        A.CFMTN_YN = 'Y'
    GROUP BY 
        B.NOW_DUTY_CD
    ORDER BY 
        B.NOW_DUTY_CD
),
UNPIVOTED_DATA AS (
    SELECT CD_NM, CD_TYPE, CNT
    FROM PIVOTED_DATA
    UNPIVOT (
        CNT FOR CD_TYPE IN (CD11, CD12, CD13, CD14, CD15, CD16, CD17, CD18, CD19, TOTAL)
    )
)
SELECT
    CD_TYPE AS 업무,
    MAX(CASE WHEN CD_NM = '운영' THEN CNT ELSE 0 END) AS 운영,
    MAX(CASE WHEN CD_NM = '운전' THEN CNT ELSE 0 END) AS 운전,
    MAX(CASE WHEN CD_NM = '토목' THEN CNT ELSE 0 END) AS 토목,
    MAX(CASE WHEN CD_NM = '건축' THEN CNT ELSE 0 END) AS 건축,
    MAX(CASE WHEN CD_NM = '기계' THEN CNT ELSE 0 END) AS 기계,
    MAX(CASE WHEN CD_NM = '전기' THEN CNT ELSE 0 END) AS 전기,
    MAX(CASE WHEN CD_NM = '신호' THEN CNT ELSE 0 END) AS 신호,
    MAX(CASE WHEN CD_NM = '통신' THEN CNT ELSE 0 END) AS 통신,
    MAX(CASE WHEN CD_NM = '업무' THEN CNT ELSE 0 END) AS 업무
FROM UNPIVOTED_DATA
GROUP BY CD_TYPE
ORDER BY CD_TYPE;

CREATE문.txt

0.00MB

TB_JBCHG_202405171109.csv

0.02MB