A칼럼 B칼럼 C칼럼 D칼럼 E칼럼 F칼럼 G칼럼
1 AAAA 원빈 20180102 이나영 20180103 장동건 20180104
2 BBBB 보아 20170102 트랙스 20170103 엑스 20170104
3 CCCC 슈퍼 20170602 레드 20170603 샤이니 20170604
4 .........
5 ...........
------------------------------------------------------테이블----------------------------------------------------------------------------------------------
결과-------------------------------
AAAA 원빈 20180102
AAAA 이나영 20180103
AAAA 장동건 20180104
BBBB 보아 20170102
BBBB 트랙스 20170103
BBBB 엑스 20170104
CCCC 슈퍼 20170602
CCCC 레드 20170603
CCCC 샤이니 20170604
WITH t AS ( SELECT 'AAAA' id, '원빈' nm1, '20180102' dt1, '이나영' nm2, '20180103' dt2, '장동건' nm3, '20180104' dt3 FROM dual UNION ALL SELECT 'BBBB', '보아', '20170102', '트랙스', '20170103', '엑스' , '20170104' FROM dual UNION ALL SELECT 'CCCC', '슈퍼', '20170602', '레드' , '20170603', '샤이니', '20170604' FROM dual ) -- 3 건 집합과 Cross Join 하여 행복제하는 방법 -- SELECT id , lv , DECODE(lv, 1, nm1, 2, nm2, 3, nm3) nm , DECODE(lv, 1, dt1, 2, dt2, 3, dt3) dt FROM t , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 3) ORDER BY id, lv ; -- UNPIVOT 11G -- SELECT * FROM t UNPIVOT ((nm, dt) FOR lv IN ((nm1, dt1) AS 1, (nm2, dt2) AS 2, (nm3, dt3) AS 3)) ORDER BY id, lv ;