Duplicate row in select statement

Question

For example, i have two rows

id|amount
 1|4
 2|1

How i can duplicate row where amount more than 1?

1|1
1|1
1|1
1|1
2|1

I have already answered a similar question here http://stackoverflow.com/a/28831409/3989608 — Lalit Kumar B, Mar 03 '15 at 15:55
possible duplicate of [Split a row based on a column value](http://stackoverflow.com/questions/28091951/split-a-row-based-on-a-column-value) — Mariano D'Ascanio, Mar 03 '15 at 15:57

score 3 · Accepted Answer · answered Mar 03 '15 at 15:55

This is easily achievable by using connect by:

with sample_data as (select 1 id, 4 amount from dual union all
                     select 2 id, 1 amount from dual)
select id, 1
from   sample_data
connect by prior id = id
           and prior dbms_random.value is not null
           and level <= amount;

        ID          1
---------- ----------
         1          1
         1          1
         1          1
         1          1
         2          1

Duplicate row in select statement

1 Answers1