Repeating rows based on the column value in each row

Question

I have 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 task 1, the repeat value is 2, so task 1 must be repeated two more times.

The final table should be lower

 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 with this question? i am using oracle 10g

Answer 1

Suppose you do not create more than 1000 lines per line:

 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 the test: http://sqlfiddle.com/#!4/4519f/12

UPDATE: As Jeffrey Kemp said, you can “detect” the maximum using 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; 

Answer 2

You can use 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 in SQL Fiddle.

Answer 3

Instead of doing this query manipulation, you can first get this data in the data table and add rows to the new data table based on the repeat value, and then bind this new data table.