RishOraDev's Oracle Blogs

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.

Data Model

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.

Requirement

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”.

Solution

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 .....

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.

References

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