dupliacte records based on gaps bettwen two columns

Question

I have this data:

   rn   sex value   from    to
    1   w   0.019500000000000000    1   25
    2   w   0.018000000000000002    26  26
    3   w   0.019500000000000000    27  28
    4   w   0.021000000000000001    29  29
    5   w   0.022500000000000002    30  30
    6   w   0.024000000000000000    31  31
    7   w   0.025500000000000001    32  32
    8   w   0.026999999999999999    33  33
    9   w   0.028500000000000001    34  34
    10  w   0.029999999999999998    35  35
    11  w   0.031500000000000000    36  36
    12  w   0.034500000000000002    37  37
    13  w   0.036000000000000004    38  38

and i would like to duplicate records based on gaps between from to so above data shoud look like this: 25

1   w   0.019500000000000000    1
1   w   0.019500000000000000    2
1   w   0.019500000000000000    3
1   w   0.019500000000000000    4
1   w   0.019500000000000000    5
1   w   0.019500000000000000    6
1   w   0.019500000000000000    7
1   w   0.019500000000000000    8
1   w   0.019500000000000000    9
1   w   0.019500000000000000    10
1   w   0.019500000000000000    11
1   w   0.019500000000000000    12
1   w   0.019500000000000000    13
1   w   0.019500000000000000    14
1   w   0.019500000000000000    15
1   w   0.019500000000000000    16
1   w   0.019500000000000000    17
1   w   0.019500000000000000    18
1   w   0.019500000000000000    19
1   w   0.019500000000000000    20
1   w   0.019500000000000000    21
1   w   0.019500000000000000    22
1   w   0.019500000000000000    23
1   w   0.019500000000000000    24
1   w   0.019500000000000000    25
2   w   0.018000000000000002    26
3   w   0.019500000000000000    27
3   w   0.019500000000000000    28

Im trying do do this by connect by clause, but no luck for now. Mayby someone has diffrent idea/approach?

Answer 1

You can use a recursive CTE in Oracle. The syntax is:

with recursive r_cte(rn, sex, value, from, to) as (
      select rn, sex, value, from, to
      from t
      union all
      select rn, sex, value, from + 1, to
      from r_cte r
      where from < to
     )
select rn, sex, value, from
from r_cte
order by rn, from;

Of course, from and to are awful column names -- because those are SQL keywords. If those are the real names, they should be escaped.

The more traditional method is to generate a list of numbers and then use these:

with n as (
      select level - 1 as n
      from (select max(t - f + 1) as range
            from t
           ) t
      connect by level <= range
     )
select rn, sex, value, f + n
from t join
     n
     on n <= (t - f)
order by rn, f + n;

Here is a db<>fiddle for this method.