get list of numbers in between two columns

Question

I have a table1(startnumber, endnumber) in oracle;

Now I need to select all the numbers lies in between startnumber and endnumber from table1

data eg.

    table1
   startnumber      endnumber
     10                15
    18                 22
    34                 50

Now I need the result set as:

10,11,12,13,14,15,18,19,20,21,22,34,35..... and so on

How do i do that

Answer 1

SQL> ed
Wrote file afiedt.buf

  1  with t as (
  2    select 10 startnum, 15 endnum from dual
  3    union all
  4    select 18, 22 from dual
  5    union all
  6    select 34, 50 from dual
  7  )
  8  select lvl
  9    from (select level lvl
 10            from dual
 11         connect by level <= (select max(endnum) from t)) a
 12   where exists (
 13      select 1
 14        from t
 15*      where lvl between t.startnum and t.endnum )
SQL> /

       LVL
----------
        10
        11
        12
        13
        14
        15
        18
        19
        20
        21
        22
        34
        35
        36
        37
        38
        39
        40
        41
        42
        43
        44
        45
        46
        47
        48
        49
        50

28 rows selected.

If the lowest start number is relatively large compared to the maximum end number, you could change the CONNECT BY subquery to generate the numbers from MIN(startnumber) to MAX(endnumber) rather than from 1 to MAX(endnumber) which should be more efficient.

Answer 2

if you wanted the values comma separated you could do as such (or if on 11gr2 use listAgg)

WITH NUMS AS(
     SELECT 10 STARTNR  , 15 ENDNR  FROM DUAL UNION
    SELECT 18 STARTNR  , 22 ENDNR  FROM DUAL UNION
    SELECT 34 STARTNR  , 50 ENDNR  FROM DUAL )       , 
 NUMSBETWEEN AS(
     SELECT distinct (level + startnr)-1 seq , startnr, endnr
       FROM NUMS
      CONNECT BY (LEVEL +STARTNR ) <= ENDNR+1
      )
SELECT startnr,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(seq,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS seq
FROM   (SELECT startnr,
               seq,
               ROW_NUMBER() OVER (PARTITION BY startnr ORDER BY seq) AS curr,
               ROW_NUMBER() OVER (PARTITION BY startnr ORDER BY seq) -1 AS prev
        FROM   numsBetween)
GROUP BY startnr
CONNECT BY prev = PRIOR curr AND startnr = PRIOR startnr
START WITH CURR = 1;

STARTNR SEQ

---

10 10,11,12,13,14,15
18 18,19,20,21,22
34 34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50