The simple example of MODEL clause.

In my previous post, i mentioned about partition outer join to make report layout.
There is another way to make a report layout using a model clause like this. It just sample for learning to MODEL clause.

 
WITH v1 AS (
           SELECT a.site ,
                  a.item ,
                  a.category ,
                  SUM( a.amount ) amount
           FROM   t2 a
           GROUP  BY a.site ,
                  a.item ,
                  a.category
     )
select s, i, c, a
from   v1
Spreadsheet
partition by (site s, item i)
dimension by (category c)
measures (amount a)
rules
(
  a['C01'] = PRESENTV(a['C01'], a['C01'], 0),
  a['C02'] = PRESENTV(a['C02'], a['C02'], 0),
  a['C03'] = PRESENTV(a['C03'], a['C03'], 0),
  a['C04'] = PRESENTV(a['C04'], a['C04'], 0),
  a['C05'] = PRESENTV(a['C05'], a['C05'], 0),
  a['C06'] = PRESENTV(a['C06'], a['C06'], 0),
  a['C07'] = PRESENTV(a['C07'], a['C07'], 0),
  a['C08'] = PRESENTV(a['C08'], a['C08'], 0),
  a['C09'] = PRESENTV(a['C09'], a['C09'], 0),
  a['C10'] = PRESENTV(a['C10'], a['C10'], 0)
)
order by s,i,c
 
call     count       cpu    elapsed       disk      query    current       rows        mis   Wait Ela
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.25       1.90        451        277          1          0          1       0.00
Exec         1      0.00       0.00          0          0          0          0          0       0.00
Fetch    40000    143.09     197.66     101622      22029         17     999995          0     191.56
------- ------  -------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Total    40002    143.34     199.55     102073      22306         18     999995          1     191.56
 
Event waited on                             Count Zero Ela     Elapse   AVG(Ela)   MAX(Ela)   Blocks
---------------------------------------- -------- -------- ---------- ---------- ---------- --------
Disk file operations I/O                        1        0       0.00       0.00       0.00        0
direct path write temp                       1561        0      41.10       0.03       0.28        0
SQL*Net message to client                   40001        0       0.26       0.00       0.00        0
direct path read                               67        0       1.39       0.02       0.25        0
SQL*Net message from client                 40001        0     153.81       0.00      18.39        0
direct path read temp                        1194        0      13.38       0.01       0.19        0
 
Rows       Row Source Operation
---------- ---------------------------------------------------
    999995 SORT ORDER BY (cr=22029 pr=101622 pw=79781 time=178073503 us cost=6243 size=226319654 card=709466)
    999995  SQL MODEL ORDERED FAST (cr=22029 pr=61614 pw=39773 time=67399100 us cost=6243 size=226319654 card=709466)
    499995   HASH GROUP BY (cr=22029 pr=39883 pw=18042 time=30056894 us cost=6243 size=226319654 card=709466)
    499995    TABLE ACCESS FULL T2 (cr=22029 pr=21841 pw=0 time=2490717 us cost=6089 size=226319654 card=709466)