Alex Rivera | Logout

Repeating rows based on column value in each row

Asked 2012-06-26T05:34:43.130
9

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

sql oracle-database
Edit
Report

1 Answer

10

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.

answered 2012-06-26T05:51:49.800

Your Answer