Generate range of numbers between two numbers

Question

I could use some help with a query to expand a CSV I'm working with, but I don't know the best way to handle the query. My data looks like this:

ID-Begin | ID-End | Color | Dwelling
-------------------------------------
79000    | 79999  | Red   | Condo
82100    | 82600  | Blue  | House
etc

I need to generate a range between the beginning ID and ending ID, and then duplicate the color and dwelling entries for that range. This is what I'm hoping to achieve:

 ID   |  Color  | Dwelling
------------------------
79000 |  Red    | Condo
79001 |  Red    | Condo
79002 |  Red    | Condo
.....
79999 |  Red    | Condo
82100 |  Blue   | House
82101 |  Blue   | House
.....
82600 |  Blue   | House

I've see other methods that allow me to generate a single range of numbers, but nothing that pulls the beginning and ending numbers from the columns in a table.

Anyway help is greatly appreciated!!

Storm

Use an inner join to a numbers table. – Zohar Peled May 17 '17 at 04:19 — Zohar Peled, May 17 '17 at 04:19

score 5 · Answer 1 · answered May 17 '17 at 05:05

Try this:

declare @content table ([ID-Begin] int,[ID-End] int,Color char(20),Dwelling char(20))
insert into @content values(79000,79999,'Red' ,'Condo')
insert into @content values(82100,82600,'Blue','House')

;with cte as (
    select [ID-Begin] ,[ID-End] ,Color,Dwelling
    from @content
    union all
    select [ID-Begin]+1,[ID-End],Color,Dwelling
    from cte
    where [ID-Begin]+1<=[ID-End]
    )
    select [Id-Begin],Color,Dwelling from cte order by [ID-Begin] 
    option (maxrecursion 10000)

TriV · Accepted Answer · 2017-05-17T05:38:33.757

5

You could do it with a recursive CTE like this

;WITH temp AS
(
   SELECT 1 AS ID 
   UNION ALL 
   SELECT t.ID + 1 FROM temp t
   WHERE t.ID < 100000
) -- return table with id from 1 to 100000
SELECT t.ID,  y.Color, y.Dwelling
FROM YourTable y
INNER JOIN temp t ON t.ID BETWEEN y.IdBegin AND y.IdEnd
OPTION (MAXRECURSION 0)

edited May 17 '17 at 05:38

answered May 17 '17 at 05:06

TriV

5,118
2
10
18

score 2 · Answer 3 · answered May 17 '17 at 05:05

2

use a Number or Tally table

SELECT n.number as ID, t.Color, t.Dwelling
FROM   yourtable t
       INNER JOIN number_table n ON  n.number >= t.ID_Begin
                                 AND n.number <= t.ID_End

answered May 17 '17 at 05:05

Squirrel

23,507
4
34
32

score 1 · Answer 4 · edited May 23 '17 at 12:18

The easiest (and probably best) way to do this is to use a numbers table. If you don't already have a numbers table, read this Stackoverflow post on how to create it. If you want to know more about what is a numbers table and why do you want one, read this blog post by Jeff Moden.

Once you have a numbers table, all you need is to use an inner join:

SELECT Number as Id,
       Color,
       Dwelling
FROM YourTable 
INNER JOIN Numbers ON Number >= [ID-Begin] 
                  AND Number <= [ID-End]

score 1 · Answer 5 · answered Oct 01 '20 at 08:14

Update for SQL 2017 and later: If the sequence you desire is < 8k then this will work:

Declare @start_num int = 1000
,   @end_num int = 1050

Select [number] = @start_num + ROW_NUMBER() over (order by (Select null))
from string_split(replicate(' ',@end_num-@start_num-1),' ')

D-Shih · Answer 6 · 2022-06-05T15:10:41.377

If your SQL-server version is higher than 2022, we can try to use GENERATE_SERIES with CROSS APPLY and let [ID-Begin] and [ID-End] be START and STOP parameters and let query be elegant and more efficient.

START： The START parameter is the first value in the interval. The START parameter is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric.
STOP： The STOP parameter is the last value in the interval. The STOP parameter is specified as a variable, a literal, or a scalar expression of type tinyint, smallint, int, bigint, decimal, or numeric. The series stops once the last generated step value exceeds the STOP value.

GENERATE_SERIES returns a single-column table containing a sequence of values in which each differs from the preceding by STEP

please make sure the compatibility_level is right

SELECT Id.[Value],
       t1.Color,
       t1.Dwelling
FROM T t1
CROSS APPLY GENERATE_SERIES(START = [ID-Begin], STOP = [ID-End]) Id

Generate range of numbers between two numbers

6 Answers6

Linked