SQL Snippets ™: Tutorials for Oracle Developers

With MODEL queries there is no need to use the join technique described in Join Method. We can generate multiple series by applying a FOR loop to each row in the base table with the aid of a PARTITION BY clause.

set null "(null)"

break on key duplicates skip 1

select
  key, key2, qty, integer_value
from
  t3
model
  PARTITION BY ( KEY )
  dimension by ( 1 as key2 )
  measures     ( qty, cast( null as integer ) as integer_value )
  rules        ( integer_value[ FOR KEY2 FROM 1 TO QTY[1] INCREMENT 1 ] = cv(key2) )
order by
  key, integer_value
;
 

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

b            1          0 (null)

c            1          1             1

d            1          2             1
d            2 (null)                 2

e            1          3             1
e            2 (null)                 2
e            3 (null)                 3