--각 행의 수 늘리기
--==================
/*
문제: http://www.soqool.com/servlet/board?cmd=view&cat=100&subcat=1010&seq=94&page=1&position=1

테이블 test가 아래와 같이 주어질 때,

WITH TEST AS (
  SELECT 'a' col1, 2 col2 FROM DUAL UNION ALL
  SELECT 'b', 3 FROM DUAL UNION ALL
  SELECT 'c', 4 FROM DUAL)

아래와 같이, 각 행의 수를 col2 만큼 늘려서 출력하는 쿼리를 만들어 보자.

col1 col2
---------
a    1
a    2
b    1
b    2
b    3

*/
--이게 더 좋다.
WITH TEST AS (
  SELECT 'a' col1, 2 col2 FROM DUAL UNION ALL
  SELECT 'b', 3 FROM DUAL UNION ALL
  SELECT 'c', 4 FROM DUAL)
SELECT
  COL1
, COL2
FROM TEST A INNER JOIN (SELECT LEVEL SEQ FROM DUAL CONNECT BY LEVEL <= 1000) B
ON B.SEQ <= A.COL2 
ORDER BY 1

--별로 안 좋다.
WITH TEST AS (
  SELECT 'a' col1, 2 col2 FROM DUAL UNION ALL
  SELECT 'b', 3 FROM DUAL UNION ALL
  SELECT 'c', 4 FROM DUAL)
SELECT
  COL1
, COL2
, RowNo
FROM(
      SELECT
        COL1
      , COL2
      , ROW_NUMBER() OVER(PARTITION BY COL1 ORDER BY COL1) RowNo
      FROM TEST
      CONNECT BY LEVEL <= COL2) T
WHERE RowNo <= COL2