SQL Common Table Expressions generating values
Example
Most databases do not have a native way of generating a series of numbers for ad-hoc use; however, common table expressions can be used with recursion to emulate that type of function.
The following example generates a common table expression called Numbers with a column i which has a row for numbers 1-5:
--Give a table name `Numbers" and a column `i` to hold the numbers
WITH Numbers(i) AS (
--Starting number/index
SELECT 1
--Top-level UNION ALL operator required for recursion
UNION ALL
--Iteration expression:
SELECT i + 1
--Table expression we first declared used as source for recursion
FROM Numbers
--Clause to define the end of the recursion
WHERE i < 5
)
--Use the generated table expression like a regular table
SELECT i FROM Numbers;
| i |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
This method can be used with any number interval, as well as other types of data.
