데이터베이스 질문과 답변
안녕하세요
컬럼1,컬럼2 결과값을 union all로 로우단위값으로 변환해서 보여주고있는데 함수를 통한 다른방법은 없는지 궁금합니다.
by ㅇㅇ준
[2020.03.25 11:08:08]
WITH TMP AS (
SELECT 'A' COL1, 1 COL2, 2 COL3,3 COL4,6 COL5 FROM DUAL UNION ALL
SELECT 'B' COL1, 1 COL2, 2 COL3,5 COL4,6 COL5 FROM DUAL
)
SELECT COL1, VAL
FROM TMP
UNPIVOT INCLUDE NULLS ( VAL FOR COL IN ( COL2, COL3, COL4,COL5) );
by 마농
[2020.03.25 13:01:24]
WITH t AS
(
SELECT 1 pk, 2 c1, 3 c2 FROM dual
UNION ALL SELECT 2, 3, 4 FROM dual
)
-- Union All --
SELECT pk, 1 lv, c1 FROM t
UNION ALL
SELECT pk, 2 lv, c2 FROM t
ORDER BY pk, lv
;
-- 2건 짜리 집합과 Cross Join 하여 행 복제
SELECT pk
, lv
, DECODE(lv, 1, c1, 2, c2) c
FROM t
, (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 2) copy_t
ORDER BY pk, lv
;
-- Unpivot --
SELECT *
FROM t
UNPIVOT (c FOR gb IN (c1, c2))
ORDER BY pk, gb
;
) 버튼을 클릭하여 작성 하시면 됩니다.