[Oracle]여러 행과 열의 위치변환(pivot, unpivot)
Oracle 11g 이상에서 지원하는 Pivot, Unpivot을 이용한다.
아래 쿼리를 참조하자
WITH T AS( SELECT 'A' GUBUN, 100 PLAN, 90 RECORD, 10 RATE FROM DUAL UNION ALL SELECT 'B' GUBUN, 200 PLAN, 150 RECORD, 40 RATE FROM DUAL UNION ALL SELECT 'C' GUBUN, 150 PLAN, 140 RECORD, 5 RATE FROM DUAL) SELECT * FROM T UNPIVOT (VALUE FOR COL IN (PLAN, RECORD, RATE)) PIVOT (MIN(VALUE) FOR GUBUN IN ('A' A, 'B' B, 'C' C)) ORDER BY DECODE(COL, 'PLAN', 1, 'RECORD', 2, 'RATE', 3) ASC;
원본 :
GUBUN PLAN RECORD RATE A 100 90 10 B 200 150 40 C 150 140 5
결과 :
COL A B C PLAN 100 200 150 RECORD 90 150 140 RATE 10 40 5
최신 댓글