Fun with Oracle Analytics SQL #1 : PIVOTS [ PART I ]
Creating a “Pivot Table” using a spreadsheet is pretty straight forward, and it enables you to transpose rows into columns. But, can we do the same operation using Oracle SQL?
Yes, it can be done, indeed. The pivot_clause of the Select statement serves this purpose. It enables us to rotate rows into columns, aggregate the data in the process of rotation, and present it in a more usable crosstabular format. In this post, I’ll show how to use Oracle’s Pivoting Operation to achieve the same result that we get using a Pivot Table in a spreadsheet. Before going into the details, let’s try to understand the data-model which I would be working on.
Rather than using the conventional employee-department tables, I will use few tables within Oracle E-Business Suite, which holds the information of Orders. The tables used and the relation between them are shown below.
To make it easier to understand, I will create a view called XXRM_ORDER_SUMMARY_2010.
CREATE OR REPLACE VIEW APPS.XXRM_ORDER_SUMMARY_2010 AS SELECT hou.name organization , ott.name Order_Type , ooha.order_number , oola.line_number , ooha.ordered_date , EXTRACT( MONTH FROM ooha.ordered_date) Month_ID , TO_CHAR(ooha.ordered_date,'MONTH' ) Month ,'Q_'|| TO_CHAR(ooha.ordered_date,'Q' ) Qtr , ooha.shipping_method_code , flv.meaning Item_Type,msib.segment1 Item , oola.ordered_quantity , oola.UNIT_SELLING_PRICE , (oola.unit_selling_price * oola.ordered_quantity ) line_total_cost FROM apps.oe_order_headers_all ooha , apps.oe_order_lines_all oola , apps.oe_transaction_types_tl ott , apps.mtl_system_items_b msib , apps.fnd_lookup_values flv , apps.hr_operating_units hou WHERE ooha.header_id = oola.header_id AND ott.transaction_type_id = ooha.order_type_id AND hou.organization_id = ooha.org_id AND oola.inventory_item_id = msib.inventory_item_id AND oola.ship_from_org_id = msib.organization_id AND msib.item_type = flv.lookup_code AND lookup_type = 'ITEM_TYPE' AND ooha.ordered_date BETWEEN TO_DATE('01-JAN-2010','DD-MON-RRRR') AND TO_DATE('31-DEC-2010','DD-MON-RRRR');
The view contains details of all the orders which were created in the year 2010. Each order might have more than one order line associated with it, for different items, and the corresponding quantities per line that was added as part of the order. A subset of the dataset from the view is shown below.
First, I’m going to find out the sum of the ordered quantities grouped by the Organization and Quarter.
SELECT Organization, Qtr, SUM(ordered_quantity) total_qty FROM APPS.XXRM_ORDER_SUMMARY_2010 GROUP BY Organization, Qtr ORDER BY 1;
The table on the left of the screenshot below, shows the resultset from the above query. Our task is to transpose the dataset on the left to a more readable format, as shown in the table on the right.
People familiar with data warehousing terminologies would call the Total_Qty column as a Fact. The columns Organization and Quarter are referred to as Dimensions of the Sales Order Entity. For anyone who is not versed with these terms, a Fact is a quantitative piece of information or a measure, whereas Dimensions are nothing but descriptive attributes of an entity.
So here, we will be pivoting the Quarter rows into columns, i.e., we’ll be “Pivoting a single measure and dimension”.
If we want to perform this operation manually, it may be done like this:
SQL> SELECT Organization 2 , SUM( CASE Qtr WHEN 'Q_1' THEN ordered_quantity END ) AS Q_1 3 , SUM( CASE Qtr WHEN 'Q_2' THEN ordered_quantity END ) AS Q_2 4 , SUM( CASE Qtr WHEN 'Q_3' THEN ordered_quantity END ) AS Q_3 5 , SUM( CASE Qtr WHEN 'Q_4' THEN ordered_quantity END ) AS Q_4 6 FROM APPS.XXRM_ORDER_SUMMARY_2010 7 GROUP BY Organization 8 ORDER BY 1; ORGANIZATION Q_1 Q_2 Q_3 Q_4 _____________________ ________ ________ ________ _______ Vision Belgium 5944 5788 4889 1117 Vision France 13868 12895 13554 1801 Vision Germany 20813 20814 21878 2886 Vision Industries 24517 19782 16921 3358 Vision Italy 10119 6948 8060 1282 Vision Netherlands 17993 17360 17378 2423 Vision Operations 65931 64630 68230 8372 Vision Sweden 14002 15845 17851 2058 8 rows selected.
The Explain Plan for the above query is shown below.
However, the same result can be achieved by writing much less code using the pivot_clause. The basic syntax for using a PIVOT is as follows:
SELECT .... FROM <table-expr> PIVOT ( aggregate-function(<column>) AS <alias> FOR <pivot-column> IN (<value1>, <value2>,..., <valueN>) ) AS <alias> WHERE .....
PIVOT : The PIVOT keyword instructs Oracle engine to transpose rows into columns.
aggregate-function : A Pivot operation demands an aggregate operation to be performed
FOR : The column(s) which is/are to be pivoted should be specified
IN : maps the columns that would be created in the output against which the aggregated measure will be calculated.
So our query to pivot the quarter column would become.
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; ORGANIZATION 'Q_1' 'Q_2' 'Q_3' 'Q_4' _____________________ ________ ________ ________ ________ Vision Belgium 5944 5788 4889 1117 Vision France 13868 12895 13554 1801 Vision Germany 20813 20814 21878 2886 Vision Industries 24517 19782 16921 3358 Vision Italy 10119 6948 8060 1282 Vision Netherlands 17993 17360 17378 2423 Vision Operations 65931 64630 68230 8372 Vision Sweden 14002 15845 17851 2058 8 rows selected.
Instead of explicitly aggregating the total quantity for each quarter, we just include them in the IN. The explain plan for the query is similar to the manual one.
Hope this is helpful. In subsequent posts, we’ll further extend and refine this query.