Repeating rows based on column value in each row

Question

I've this table with the following data

Job  Quantity   Status  Repeat 
1    100         OK     2 
2    400         HOLD   0 
3    200         HOLD   1 
4    450         OK     3 

Based on the value in the Repeat column for each row, the row should be repeated again. For example for the Job 1, Repeat value is 2 so the Job 1 should repeat two more times.

The resultant table should be as below

Job      Quantity   Status  Repeat 
1        100        OK      2 
1        100        OK      2 
1        100        OK      2 
2        400        HOLD    0 
3        200        HOLD    1 
3        200        HOLD    1 
4        450        OK      3 
4        450        OK      3 
4        450        OK      3 
4        450        OK      3 

Can someone please help me out with this query? am using oracle 10g

Solution 1

Supposing you won't generate more than 1000 rows per row:

with num as (select level as rnk from dual connect by level<=1000)
select Job,  Quantity,   Status,  Repeat, rnk 
from t join num on ( num.rnk <= repeat )
order by job, rnk;

Here is a test: http://sqlfiddle.com/#!4/4519f/12

UPDATE: As Jeffrey Kemp said, you can "detect" the maximum with a subquery:

with num as (select level as rnk 
             from dual 
             connect by level<=(select max(repeat) from t)
             )
select job,  quantity,   status,  repeat, rnk 
from t join num on ( num.rnk <= repeat )
order by job, rnk;

Solution 2

You could use a recursive CTE:

with    cte(Job, Repeat, i) as 
        (
        select  Job
        ,       Repeat
        ,       0
        from    YourTable
        union all
        select  Job
        ,       Repeat
        ,       i + 1
        from    cte
        where   cte.i < cte.Repeat
        )
select  *
from    cte
order by
        Job
,       i

Live example at SQL Fiddle.

Comments

[Jeffrey Kemp]

+1, in addition you could replace 1000 with a subquery to find the max value for repeat.