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.
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
In Oracle Database you can generate rows using the
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 )
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)
DB2 and SQL Server
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.
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
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. 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 = 1 array = 2 array = 3 array = 4 array = 5 array = 6 array = 7 array = 8 array = 9 array = 10
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.
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.