Pivot Query Using Oracle Model Clause

The example shows the use of Oracle Model clause to create pivot table/report. In the example shown below, the annual data (counts) is displayed on a quarterly basis. This is based on SQL Modeling syntax discussed in the website pivoting-in-sql-using-the-10g-model-clause. In the query below data is summarized on a quarterly basis and split into fours quarters per the specific year (refer quarter query). The syntax shown below allows data to be pivoted on non-existant columns Qtr1, Qtr2, Qtr3 and Qtr4.

Pivoting function in Oracle, refer to Oracle 11g Pivot

SELECT
   year_no, qtr1, qtr2, qtr3, qtr4,
   NVL(qtr1,0)+NVL(qtr2,0)+NVL(qtr3,0)+NVL(qtr4,0)  Yearly_total
FROM
 (SELECT  q_tab.year_no, q_tab.qtr, SUM(q_tab.counts) qty
  FROM
   (SELECT TO_CHAR(transaction_date,'yyyy') year_no,
      DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),4),
       TO_NUMBER(TO_CHAR(transaction_date,'mm')),
       DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),7),
        TO_NUMBER(TO_CHAR(transaction_date,'mm')),
        DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),10),
         TO_NUMBER(TO_CHAR(transaction_date,'mm')),4,3),2),1)  qrt,
     COUNT(*) counts  
    FROM wip_transactions
    GROUP BY
     TO_CHAR(transaction_date,'mm'),
        DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),4),
          TO_NUMBER(TO_CHAR(transaction_date,'mm')),
          DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),7),
           TO_NUMBER(TO_CHAR(transaction_date,'mm')),
           DECODE(GREATEST(TO_NUMBER(TO_CHAR(transaction_date,'mm')),10),
            TO_NUMBER(TO_CHAR(transaction_date,'mm')),4,3),2),1),
  TO_CHAR(transaction_date,'yyyy')) q_tab
 GROUP BY
 q_tab.year_no, q_tab.qtr) q_qtr_sum
MODEL 
 RETURN UPDATED ROWS
 PARTITION BY (q_qtr_sum.year_no)
 DIMENSION BY (q_qtr_sum.qtr)
 MEASURES     (q_qtr_sum.qty, 0 qtr1, 0 qtr2, 0 qtr3, 0 qtr4)
 RULES UPSERT
  (qtr1 [0] = qty [1],
   qtr2 [0] = qty [2],
   qtr3 [0] = qty [3],
   qtr4 [0] = qty [4])
ORDER BY year_no;




Pivoting using dynamic sql, refer to dynamic sql

Oracle registered trademark of Oracle Corporation.

Last Revised On: October 07, 2014

72948