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

oracle

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

Best Answer

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

Related Question