Repeat row in the result based on the value of a column 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
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 &lt;= 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 &gt; 0
)
select first_col, val_col
from counter
order by first_col;


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