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

Comments

st's picture

Yes

Submitted by st on

Yes, it works with recursive queries, too (up to 99), thank you.