Submitted by st on
Here are some examples of SQL to generate a sequence of numeric (integer) values from 0 to 99.
ANSI-compatible
SELECT ones.n1 + tens.n2 * 10 FROM ( SELECT 0 AS n1 UNION SELECT 1 AS n1 UNION SELECT 2 AS n1 UNION SELECT 3 AS n1 UNION SELECT 4 AS n1 UNION SELECT 5 AS n1 UNION SELECT 6 AS n1 UNION SELECT 7 AS n1 UNION SELECT 8 AS n1 UNION SELECT 9 AS n1 ) ones CROSS JOIN ( SELECT 0 AS n2 UNION SELECT 1 AS n2 UNION SELECT 2 AS n2 UNION SELECT 3 AS n2 UNION SELECT 4 AS n2 UNION SELECT 5 AS n2 UNION SELECT 6 AS n2 UNION SELECT 7 AS n2 UNION SELECT 8 AS n2 UNION SELECT 9 AS n2 ) tens
More specific but shorter Transact SQL version.
SELECT ones.n + tens.n * 10 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) ORDER BY 1
The solution without number limitation (slightly SQL Server-specific)
WITH n1(n) AS (SELECT 1 UNION ALL SELECT 1), -- returns 1 row n2(n) AS (SELECT 1 FROM n1 AS x, n1 AS y), -- 4 rows n3(n) AS (SELECT 1 FROM n2 AS x, n2 AS y), -- 16 n4(n) AS (SELECT 1 FROM n3 AS x, n3 AS y), -- 256 n5(n) AS (SELECT 1 FROM n4 AS x, n4 AS y), -- 65 536 n6(n) AS (SELECT 1 FROM n5 AS x, n5 AS y), -- 4 294 967 296 nums(n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM n6) SELECT TOP(100 /* set limit here */) n FROM nums
Comments
Another option
Submitted by Ondrej Kelle (not verified) on
Recursive CTE:
Yes
Submitted by st on
Yes, it works with recursive queries, too (up to 99), thank you.