Get List Of Numbers In Between Two Columns With Key

I want to get the list of numbers in between two columns. A tables values will be used to generate more rows. e.g Table1: Key StartNum EndNum --- -------- ------ A 1 3 B

Solution 1:

a_horse_with_no_name-s solution would be

SELECTdistinctKey,(level + StartNum)-1 Num
   FROM Table1
  CONNECT BY (LEVEL +StartNum ) <= EndNum+1orderbyKey, Num

Output:

A1A2A3B6B7B8

But I'd prefer creating a global temporary table and populate it from plsql, as the above method contains subsequent decarts on the table (thus the distinct required). http://www.dba-oracle.com/t_temporary_tables_sql.htm

Solution 2:

This is a slightly adapted version of Justin's solution posted in: get list of numbers in between two columns

selectkey, num 
from (
  selectdistinct t1.key, t1.startnum + level - 1 num, t1.startnum, t1.endnum
  from table1 t1
  connect by level <= (select t2.endnum from table1 t2 where t1.key = t2.key)
) t
where num between t.startnum and t.endnum
orderbykey, num

I'm not happy with the need for the distinct in the inner query, but I currently don't have the time to dig deeper into this.

Solution 3:

Try this,

SELECT t.StartNum , t.StartNum , ROWNUM
FROM Table1 t , ALL_OBJECTS
WHERE ROWNUM between t.StartNum  and t.StartNum 

Solution 4:

Create a store procedure in transact SQL

CreateProcedure GetRangeFromTable 
AsBegincreatetable #Result(           
            code varchar(50),
            num int
    )  

    Declare@codevarchar(50),
     @startint ,
     @endintDECLARE num_cursor CURSORFORSelect*from Table1
    OPEN num_cursor

    FETCH NEXT FROM num_cursor 
    INTO@code, @start, @end


    WHILE @@FETCH_STATUS =0BEGIN

        While @start<=@endBeginInsertinto #Result(code,num) Values (@code,@start)
            Set@start=@start+1EndFETCH NEXT FROM num_cursor 
       INTO@code, @start, @endENDSelect*from #ResultCLOSE num_cursor
    DEALLOCATE num_cursor

End