SQL: generate list (sequence) of integer values
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