CODEWITHSUNDEEP
sqloracle-database
user991255
user991255

Reputation: 355

Repeating rows based on column value in each row

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

Upvotes: 9

Views: 22940

Answers (3)

Florin Ghita
Florin Ghita

Reputation: 17643

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;

Upvotes: 7

Andomar
Andomar

Reputation: 238078

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.

Upvotes: 10

Sumant
Sumant

Reputation: 163

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

Upvotes: -2

Related Questions