RishOraDev's Oracle Blogs

In my previous post, I showed how to rotate rows into column using the PIVOT operation in Oracle. I pivoted a single measure and dimension. But wait! When we create a PIVOT Table in spreadsheet, we get additional aggregated columns, a. showing the total quantity of items ordered across all organizations in each quarter, and b. showing the total quantity of items ordered across all four quarters for each organization.

Can we achieve this using Oracle SQL. You bet we can, let’s see how. Just to recap, the query that we used to pivot the quarter column is as follows:

SQL> SELECT * FROM
  2  (        SELECT Organization, Qtr,  ordered_quantity
  3           FROM APPS.XXRM_ORDER_SUMMARY_2010
  4           ORDER BY 2,1
  5  )
  6  PIVOT( SUM(ordered_quantity)
  7          FOR Qtr IN('Q_1','Q_2','Q_3','Q_4')
  8  )
  9  ORDER BY 1;

Let us add the CUBE extension to the inline view of the above query. This will generate the subtotals for all possible combinations of the dimensions specified, in this case for both Organization and Qtr.

SQL> SELECT   Organization
  2         , Qtr
  3         , SUM(ordered_quantity) ordered_quantity
  4  FROM APPS.XXRM_ORDER_SUMMARY_2010
  5  GROUP BY CUBE(Organization, Qtr)
  6  ORDER BY 1;

I have exported the the output of the above query and tried to map that to the results of the PIVOT Table that we get from the spreadsheet, for some rows.

We are nearly there, but not quiet, as we want the output of our query to be in the exact same format as we get from a spreadsheet. How do we do that?

The PIVOT clause come to the rescue again. But before applying the PIVOT, I need to add NVL to the two dimentions – Organization and Qtr, like this:

NVL(Organization,'Grand Total')
NVL(Qtr,'Grand Total')

Did I miss anything? Yes, the final thing I need to do is include the ‘Grand Total’ column in the PIVOT clause, because we want to tell Oracle to use Grand Total as one of the pivot columns too. Like this:

FOR Qtr IN('Q_1','Q_2','Q_3','Q_4', 'Grand Total')

So my final query would be:

SQL> SELECT * From
  2  (        SELECT NVL(Organization,'Grand Total') Organization, NVL(Qtr,'Grand Total') Qtr,  SUM(ordered_quantity) ordered_quantity
  3          FROM APPS.XXRM_ORDER_SUMMARY_2010
  4          GROUP BY CUBE(Organization, Qtr)
  5          ORDER BY 1
  6          )
  7  PIVOT( SUM(ordered_quantity)
  8          FOR Qtr IN('Q_1','Q_2','Q_3','Q_4', 'Grand Total')
  9  )
 10  ORDER BY 1 desc;

Execute this query now and voila!!!

Hope this is helpful. In the next post I’ll show how to use Pivots for “multiple measures and dimensions”.

Related Posts

• FUN WITH ORACLE ANALYTICS SQL #1 : PIVOTS [ PART I ]

References

• SQL for Analysis and Reporting
• Practical Oracle SQL [ Authors: Berg Hansen, Kim ]