Add new comment

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

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