How to generate a range of numbers between two numbers?
sql, sql-server, sql-server-2008, t-sql
Solution
Select non-persisted values with the `VALUES` keyword. Then use `JOIN`s to generate lots and lots of combinations (can be extended to create hundreds of thousands of rows and beyond).
Short and fast version (not that easy to read):
WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n))
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM x ones, x tens, x hundreds, x thousands
ORDER BY 1
Demo
More verbose version:
SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1
Demo
Both versions can easily be extended with a `WHERE` clause, limiting the output of numbers to a user-specified range. If you want to reuse it, you can define a table-valued function for it.
Problem
I have two numbers as input from the user, like for example `1000` and `1050`. How do I generate the numbers between these two numbers, using a sql query, in seperate rows? I want this: ``` 1000 1001 1002 1003 . . 1050 ```