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
열개 컬럼 고정으로 사용
동적으로 컬럼 갯수를 정해야 할 경우 프로시져가 구현이 용이 하다