오라클 여러 컬럼 PIVOT

Dr.Green·2022년 4월 27일
0

PIVOT과 PARTITION BY를 사용하여 여러행 컬럼으로 변경하기

WITH LCC_TB AS (
    SELECT  LCC_CD
            , LCC_NAME
            , MODE_CD
            , MODE_TITLE
            , ROW_NUMBER() OVER (PARTITION BY LCC_CD ORDER BY MODE_CD, MODE_TITLE) AS MODE_NUM
    FROM (
             SELECT 'LCC_A' AS LCC_CD,'LCC code A' AS LCC_NAME,'LCC_A_01' AS MODE_CD, 'Mode 01' AS MODE_TITLE FROM DUAL
             UNION ALL
             SELECT 'LCC_A' AS LCC_CD,'LCC code A' AS LCC_NAME,'LCC_A_02' AS MODE_CD, 'Mode 02' AS MODE_TITLE FROM DUAL
             UNION ALL
             SELECT 'LCC_B' AS LCC_CD,'LCC code B' AS LCC_NAME,'LCC_B_01' AS MODE_CD, 'Mode 01' AS MODE_TITLE FROM DUAL
             UNION ALL
             SELECT 'LCC_B' AS LCC_CD,'LCC code B' AS LCC_NAME,'LCC_B_02' AS MODE_CD, 'Mode 02' AS MODE_TITLE FROM DUAL
             UNION ALL
             SELECT 'LCC_B' AS LCC_CD,'LCC code B' AS LCC_NAME,'LCC_B_03' AS MODE_CD, 'Mode 03' AS MODE_TITLE FROM DUAL
             UNION ALL
             SELECT 'LCC_C' AS LCC_CD,'LCC code C' AS LCC_NAME,'LCC_B_01' AS MODE_CD, null AS MODE_TITLE FROM DUAL
        )
)
SELECT *
FROM LCC_TB
    PIVOT ( max(MODE_CD) AS MODE_CODE , max(MODE_TITLE) AS MODE_NAME
    FOR MODE_NUM IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
    )
ORDER BY LCC_NAME

열개 컬럼 고정으로 사용

동적으로 컬럼 갯수를 정해야 할 경우 프로시져가 구현이 용이 하다

profile
모바일 퍼스트

0개의 댓글