SQL to generate a number between range specified by columns

Question

I have a table which looks like this

IDNUM | Name  | LowRange | HighRange | Notes
123   | TESTS | 100      | 201       | Hello
124   | TEST2 | 200      | 210       | 
125   | TESTS | 100      | 201       | Hello

I was wondering if there was a query that would return the following results

IDNUM | Name  | Number | Notes
123   | TESTS | 100      | Hello
123   | TESTS | 101      | Hello
123   | TESTS | 102      | Hello
123   | TESTS | 103      | Hello
......til 201
124   | TEST2 | 200      | 
124   | TEST2 | 201      |
124   | TEST2 | 202      |  
......til 210

I'm looking for a way to do this in both SQL server 2016 and Oracle 11g, but any help on either one will be appreciated

This is super simple using a tally table. http://www.sqlservercentral.com/articles/T-SQL/62867/ This works in Oracle as well as sql server (although you have to build it a little differently). Then you select from your table and join to your tally table where tally.N >= LowRange and tally.N <= HighRange. — Sean Lange, Jul 14 '17 at 14:00
Looks like a good use for a Numbers table (just a table with integers in a column). Join on >= LowRange and <= HighRange. — Jacob H, Jul 14 '17 at 14:00

score 2 · Accepted Answer · answered Jul 14 '17 at 14:01

The simplest way is a numbers table. The following will work in both Oracle and SQL Server -- assuming the base table for "numbers" is large enough:

with numbers as (
      select row_number() over (order by idnum) - 1 as n
      from t
     )
select idnum, name, lowrange + n.n as number, notes
from t join
     numbers n
     on lowrange + n.n <= highrange;

If the above does not generate enough numbers, you can use cross joins in the CTE to get more.

Each database has alternative methods of generating numbers, but this works in both databases.

score 2 · Answer 2 · answered Jul 14 '17 at 14:23

Oracle Hierarchical Query:

SELECT t.IDNUM,
       t.Name,
       n.COLUMN_VALUE AS "Number",
       t.Notes
FROM   your_table t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT t.LowRange + LEVEL - 1
             FROM   DUAL
             CONNECT BY t.LowRange + LEVEL - 1 < t.HighRange
           ) AS SYS.ODCINUMBERLIST
         )
       ) n;

Recursive Sub-query Factoring Clause:

WITH numbers ( IDNUM, Name, LowRange, HighRange, Notes ) AS (
  SELECT IDNUM, Name, LowRange, HighRange, Notes
  FROM   your_table
UNION ALL
  SELECT IDNUM, Name, LowRange + 1, HighRange, Notes
  FROM   numbers
  WHERE  LowRange < HighRange
)
SELECT IDNUM,
       Name,
       LowRange AS "Number",
       Notes
FROM   numbers;

score 0 · Answer 3 · answered Jul 14 '17 at 14:05

One way is to use the code in this accepted answer and use the following:

DECLARE @startnum INT
DECLARE @endnum INT

SELECT  @startnum = MIN(LowRange) ,
        @endnum = MAX(HighRange)
FROM    IDsAndRanges

;
WITH gen AS (
    SELECT @startnum AS num
    UNION ALL
    SELECT num+1 FROM gen WHERE num+1<=@endnum
)

SELECT  * 
FROM    [IDsAndRanges] I
LEFT
JOIN    gen G
        ON G.num BETWEEN I.LowRange AND I.HighRange
option (maxrecursion 10000)

This was written using the following test harness:

CREATE TABLE [IDsAndRanges]
(
    IDNUM       [INT],
    Name        NVARCHAR(100),
    LowRange    INT,
    HighRange   INT,
    Notes       NVARCHAR(100)
)

INSERT
INTO    [IDsAndRanges]
        (
            IDNUM, Name, LowRange, HighRange, Notes
        )
VALUES  (123, 'TESTS', 100, 201, 'Hello'),
        (124, 'TEST2', 200, 210, ''),
        (125, 'TESTS', 100, 201, 'Hello')

score 0 · Answer 4 · answered Jul 14 '17 at 14:15

You could do this as a recursive with-clause (aka recursive CTE), like so:

WITH main_data (idnum, NAME, NUM, highrange, notes) AS (SELECT idnum,
                                                               NAME,
                                                               lowrange NUM,
                                                               highrange,
                                                               notes
                                                        FROM   sample_data
                                                        UNION ALL
                                                        SELECT idnum,
                                                               NAME,
                                                               NUM + 1 NUM,
                                                               highrange,
                                                               notes
                                                        FROM   main_data
                                                       WHERE  NUM < highrange)
SELECT idnum,
       NAME,
       NUM,
       notes
FROM   main_data
ORDER BY idnum, NUM;


     IDNUM NAME         NUM NOTES
---------- ----- ---------- -----
       123 TESTS        100 Hello
       123 TESTS        101 Hello
       123 TESTS        102 Hello
       123 TESTS        103 Hello
       124 TEST2        200 
       124 TEST2        201 
       124 TEST2        202 
       124 TEST2        203 
       124 TEST2        204 
       125 TESTS        150 Hello
       125 TESTS        151 Hello
       125 TESTS        152 Hello
       125 TESTS        153 Hello
       125 TESTS        154 Hello
       125 TESTS        155 Hello
       125 TESTS        156 Hello
       125 TESTS        157 Hello
       125 TESTS        158 Hello
       125 TESTS        159 Hello
       125 TESTS        160 Hello

The above query works in Oracle (I believe it should also work in SQL Server) based on the following sample data:

SELECT 123 idnum, 'TESTS' NAME, 100 lowrange, 103 highrange, 'Hello' notes FROM dual UNION ALL
SELECT 124 idnum, 'TEST2' NAME, 200 lowrange, 204 highrange, NULL notes FROM dual UNION ALL
SELECT 125 idnum, 'TESTS' NAME, 150 lowrange, 160 highrange, 'Hello' notes FROM dual

score 0 · Answer 5 · answered Jul 14 '17 at 15:00

Maybe not a good approach but it works for me;

CREATE TYPE shin.tab_rows AS OBJECT (
  idnum           NUMBER,
  description  VARCHAR2(50),
  num_ber  NUMBER,
  notes  VARCHAR2(50)
);
/

CREATE TYPE shin.test_tab IS TABLE OF shin.tab_rows;
/


CREATE OR REPLACE FUNCTION shin.get_numbers
 RETURN shin.test_tab PIPELINED AS
BEGIN
  for records in (select IDNUM,lowrange,highrange,name,notes from shin.test_stack) LOOP

  FOR num_ber IN records.lowrange..records.highrange LOOP
    PIPE ROW(shin.tab_rows(records.IDNUM, records.name,num_ber,records.notes));  

    END LOOP; 
  END LOOP;

  RETURN;
END;
/

select * from table(shin.get_numbers)

SQL to generate a number between range specified by columns

5 Answers5