Repeat row in the result based on the value of a column

Question

FIRST_COL VAL_COL
A          2 
B          3
C         4

I need output as below.

FIRST_COL VAL_COL
A          2
A          2
B          3
B          3
B          3
C          4
C          4
C          4
C          4

example: my original table contains a=2 but I need in output as a=2 two times same for of b=3 three times c=4 four times

Answer 1

If you are using Oracle 12c or higher, you can CROSS APPLY with a CONNECT BY LEVEL query generating the numbers from 1 to VAL_COL. Something like this (I don't have an instance ready and SQLFiddle uses 11g, which doesn't support this):

SELECT FIRST_COL, VAL_COL
FROM table1
CROSS APPLY (
  SELECT LEVEL
    FROM DUAL  
    CONNECT BY LEVEL <= table1.VAL_COL
);

If you are on 11g or higher, you can use Recursive Refactored Subquery.

with data as (
  -- example data
  select 'A' FIRST_COL, 2 VAL_COL from dual union all
  select 'B' FIRST_COL, 3 VAL_COL from dual union all
  select 'C' FIRST_COL, 4 VAL_COL from dual
), counter(first_col,val_col, iterator) as (
  select first_col, val_col, val_col
  from data
  union all
  select first_col, val_col, iterator - 1
  from counter
  where iterator - 1 > 0
)
select first_col, val_col
from counter
order by first_col;

Prior to 11g, you would need to use a pipeline function

Answer 2

Yet another option:

SQL> with test (first_col, val_col) as
  2    (select 'A', 2 from dual union all
  3     select 'B', 3 from dual union all
  4     select 'C', 4 from dual
  5    )
  6  select t.first_col
  7  from test t,
  8      table(cast(multiset(select level from dual
  9                          connect by level <= t.val_col
 10                         ) as sys.odcinumberlist))
 11  order by t.first_col;

FIRST_COL
--------------------
A
A
B
B
B
C
C
C
C

9 rows selected.

SQL>