**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.