SQL Snippets ™: Tutorials for Oracle Developers

The preceding topics showed how to generate a single series of integers. Sometimes many different integer series are required in the same query. For example, given a data table like this

KEY        QTY
--- ----------
a   (null)
b            0
c            1
d            2
e            3
 

we sometimes need queries that generate results like these

KEY        QTY INTEGER_VALUE
--- ---------- -------------
a   (null)     (null)

b            0 (null)

c            1             1

d            2             1
d            2             2

e            3             1
e            3             2
e            3             3
 

where a row like the one where KEY='d' needs the integer series "1,2" but the row where KEY='e' needs the series "1,2,3".

The subtopics in this section demonstrate various ways to accomplish this.