Generate range of numbers between two numbers

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

Answer

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)

Advertisement

Answer