오라클) UNPIVOT --> 가로열 모델을 세로로 변형하여 출력

 

오라클) UNPIVOT --> 가로열 모델을 세로로 변형하여 출력

 

WITH TEMP AS
(
SELECT 'YUN' AS COL_1
      , 1    AS VAL_1
      , 2    AS VAL_2
      , 3    AS VAL_3
      , NULL AS VAL_4
FROM DUAL
UNION
SELECT 'KIM' AS COL_1
      , 11   AS VAL_1
      , 22   AS VAL_2
      , 33   AS VAL_3
      , NULL AS VAL_4
FROM DUAL
)
SELECT *
FROM TEMP
UNPIVOT
( 컬럼값 FOR 컬럼ID IN (VAL_1, VAL_2, VAL_3, VAL_4))

COL_1  컬럼ID  컬럼값

-------------------

KIM VAL_1 11
KIM VAL_2 22
KIM VAL_3 33
YUN VAL_1 1
YUN VAL_2 2
YUN VAL_3 3

---------------------

위의 질의시 널값 제외된다. 널값 포함시 UNPIVOT INCLUDE NULLS 명령어 사용한다.

WITH TEMP AS
(
SELECT 'YUN' AS COL_1
      , 1    AS VAL_1
      , 2    AS VAL_2
      , 3    AS VAL_3
      , NULL AS VAL_4
FROM DUAL
UNION
SELECT 'KIM' AS COL_1
      , 11   AS VAL_1
      , 22   AS VAL_2
      , 33   AS VAL_3
      , NULL AS VAL_4
FROM DUAL
)
SELECT *
FROM TEMP
UNPIVOT INCLUDE NULLS
( 컬럼값 FOR 컬럼ID IN (VAL_1, VAL_2, VAL_3, VAL_4))