CODEWITHSUNDEEP
sqloracle-database
Unknown.Vagrant
Unknown.Vagrant

Reputation: 252

Duplicate row in select statement

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

Upvotes: 1

Views: 72

Answers (1)

Boneist
Boneist

Reputation: 23588

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

Upvotes: 3

Related Questions