Skip to Main Content
  • Questions
  • Need to generate numbers between a given range for each record.

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Deepti.

Asked: July 07, 2016 - 8:41 am UTC

Answered by: Chris Saxon - Last updated: July 07, 2016 - 8:55 am UTC

Category: Developer - Version: 11.2

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Generating rowids

You Asked

Hi Team,

I have below sample table as input :

ID MIN MAX
1 5 10
2 3 5

And I want output as follows:
ID value
1 5
1 6
1 7
1 8
1 9
2 3
2 4

Id column is a primary key on the table.
I do not want to use cursor for the same. could you please suggest SQL statement which could help me to achieve this.

Thanks
Deepti

and we said...

All SQL statements are cursors. Saying "I don't want to use a cursor for my SQL query" is a bit like saying "I don't want to use a vehicle to drive my car"!

Anyway, to answer you question:

- Have a table of numbers. You could use a row generator or have a predefined table
- Join your table to this:

create table t (
  id int,
  mn int,
  mx int
);
insert into t values (1, 5, 10);
insert into t values (2, 3, 5);

with rws as (
  select rownum x from dual connect by level <= 10
)
  select id, x from t
  join   rws r
  on     r.x >= mn
  and    r.x < mx
  order  by 1, 2;

        ID          X
---------- ----------
         1          5
         1          6
         1          7
         1          8
         1          9
         2          3
         2          4


Is this answer out of date? If it is, please let us know via a Review