Problem
You would like to have a "row source generator” available to you in your queries. Row source generators are useful for queries that require pivoting. For example, you want to return a result set such as the following, up to any number of rows that you specify:
ID
---
1
2
3
4
5
6
7
8
9
10
…
If your RDBMS provides built-in functions for returning rows dynamically, you do not need to create a pivot table in advance with a fixed number of rows. That’s why a dynamic row generator can be so handy. Otherwise, you must use a traditional pivot table with a fixed number of rows (that may not always be enough) to generate rows when needed.
Solution
This solution shows how to return 10 rows of increasing numbers starting from 1. You can easily adapt the solution to return any number of rows.
The ability to return increasing values from 1 opens the door to many other solutions. For example, you can generate numbers to add to dates in order to generate sequences of days. You can also use such numbers to parse through strings.
DB2 and SQL Server
Use the recursive WITH
clause to generate a sequence of rows with incrementing values. Using a recursive CTE will in fact work with the majority of RDBMS’s today. Use a one-row table such as T1 to kick off the row generation; the WITH
clause does the rest:
1 with x (id)
2 as (
3 select 1
4 from t1
5 union all
6 select id+1
7 from x
8 where id+1 <= 10
9 )
10 select * from x
Following is a second, alternative solution for DB2 only. Its advantage is that it does not require table T1:
1 with x (id)
2 as (
3 values (1)
4 union all
5 select id+1
6 from x
7 where id+1 <= 10
8 )
9 select * from x
Oracle
In Oracle Database you can generate rows using the MODEL
clause:
1 select array id
2 from dual
3 model
4 dimension by (0 idx)
5 measures(1 array)
6 rules iterate (10) (
7 array[iteration_number] = iteration_number+1
8 )
PostgreSQL
Use the very handy function GENERATE_SERIES
, which is designed for the express purpose of generating rows:
1 select id
2 from generate_series (1, 10) x(id)
Discussion
DB2 and SQL Server
The recursive WITH
clause increments ID (which starts at 1) until the WHERE
clause is satisfied. To kick things off you must generate one row having the value 1. You can do this by selecting 1 from a one-row table or, in the case of DB2, by using the VALUES
clause to create a one-row result set.
Oracle
In the MODEL
clause solution, there is an explicit ITERATE
command that allows you to generate multiple rows. Without the ITERATE
clause, only one row will be returned, since DUAL
has only one row. For example:
select array id
from dual
model
dimension by (0 idx)
measures(1 array)
rules ()
ID
--
1
The MODEL
clause not only allows you array access to rows, it allows you to easily “create” or return rows that are not in the table you are selecting against. In this solution, IDX is the array index (location of a specific value in the array) and ARRAY (aliased ID) is the “array” of rows. The first row defaults to 1 and can be referenced with ARRAY[0]. Oracle provides the function ITERATION_NUMBER
so you can track the number of times you’ve iterated. The solution iterates 10 times, causing ITERATION_NUMBER
to go from 0 to 9. Adding 1 to each of those values yields the results 1 through 10.
It may be easier to visualize what’s happening with the model clause if you execute the following query:
select 'array['||idx||'] = '||array as output
from dual
model
dimension by (0 idx)
measures(1 array)
rules iterate (10) (
array[iteration_number] = iteration_number+1
)
OUTPUT
------------------
array[0] = 1
array[1] = 2
array[2] = 3
array[3] = 4
array[4] = 5
array[5] = 6
array[6] = 7
array[7] = 8
array[8] = 9
array[9] = 10
PostgreSQL
All the work is done by the function GENERATE_SERIES
. The function accepts three parameters, all numeric values. The first parameter is the start value, the second parameter is the ending value, and the third parameter is an optional “step” value (how much each value is incremented by). If you do not pass a third parameter, the increment defaults to 1.
The GENERATE_SERIES
function is flexible enough so that you do not have to hardcode parameters. For example, if you wanted to return five rows starting from value 10 and ending with value 30, incrementing by 5 such that the result set is the following:
ID
---
10
15
20
25
30
you can be creative and do something like this:
select id
from generate_series(
(select min(deptno) from emp),
(select max(deptno) from emp),
5
) x(id)
Notice here that the actual values passed to GENERATE_SERIES
are not known when the query is written. Instead, they are generated by subqueries when the main query executes.