[Oracle] 각 Row를 컬럼에 지정된 숫자만큼 복제하기

[Oracle] 각 Row를  컬럼에 지정된 숫자만큼 복제하기

WITH TEMP_DATA AS ( SELECT 'A' Col1, '20180910' Col2, 3 Col3 FROM DUAL UNION ALL SELECT 'B' Col1, '20180911' Col2, 2 Col3 FROM DUAL UNION ALL SELECT 'C' Col1, '20180913' Col2, 1 Col3 FROM DUAL UNION ALL SELECT 'D' Col1, '20180930' Col2, 5 Col3 FROM DUAL ) SELECT Col1, Col2, Col3, TO_CHAR(TO_DATE(A.Col2) + B.NUM,'YYYYMMDD') Col4 FROM TEMP_DATA A CROSS JOIN ( SELECT LEVEL NUM FROM DUAL CONNECT BY LEVEL < 6 -- 6: Max값 + 1 ) B WHERE B.NUM BETWEEN 1 AND A.Col3 ORDER BY A.Col1,TO_CHAR(TO_DATE(A.Col2) + B.NUM,'YYYYMMDD');

<Result>

C COL2           COL3 COL4    

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

A 20180910          3 20180911

A 20180910          3 20180912

A 20180910          3 20180913

B 20180911          2 20180912

B 20180911          2 20180913

C 20180913          1 20180914

D 20180930          5 20181001

D 20180930          5 20181002

D 20180930          5 20181003

D 20180930          5 20181004

D 20180930          5 20181005