Expand a range columns into a single column

oracleselect

I'm thinking this is an easy query but I don't know how to start. I have a database table called Booklet with columns id, controlNoFrom and controlNoTo. Sample data looked like this:

id       | controlNoFrom | controlNoTo
---------+---------------+-------------
20150105 |             1 |         100
20150106 |           101 |         150
20150107 |           151 |         225 
---------+---------------+-------------

What I want is a query that can present the data like this:

id       | controlNo
---------+-----------
20150105 |         1 
20150105 |         2
20150105 |         3
...      |       ...
20150105 |        99 
20150105 |       100
20150106 |       101
20150106 |       102
...      |       ...
20150106 |       149
20150106 |       150
20150107 |       151
20150107 |       152
...      |       ...
20150107 |       224
20150107 |       225
---------+-----------

It's easy if it is the other way around, but I don't know what kind of SQL to be used in this scenario. What would you call this approach and what would the SQL statement in OracleDB look like?

Best Answer

You could use some kind of row generation technique, for example:

select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)

This generates as many rows as the largest range requires, with data 0, 1, 2, ..., starting with 0, so no correction needed, when we add these values to controlnofrom. Now simply join this to your original table and limit the number of rows by controlnoto - controlnofrom, finally add the row number to controlnofrom:

with booklet as
(
  select '20150105' as id, 1 as controlnofrom, 100 as controlnoto from dual union all
  select '20150106', 101, 150 from dual union all
  select '20150107', 151, 225 from dual
)
select
  b.id as id, b.controlnofrom + r.q as controlno
from
  booklet b
  join (select rownum - 1 as q from dual connect by level <= (select max(controlnoto - controlnofrom) + 1 from booklet)) r on (r.q <= b.controlnoto - b.controlnofrom )
order by
  id, controlno
;
Related Question