Pivot Script

Pivot Table

The sales/revenue data is typically stored in a data warehouse architecture for BI reporting purposes in FACT-DIMENSION tables. The table shown here (as shown in figure 1) is for analysis and creation of pivot report. Shown below is static pivot report query that splits sales data into the months of a year using DECODE function. For dynamic pivoting, the pivot column has to be built using dynamic sql

Refer: Oracle 11g Pivot Function

Figure 1

Static Pivot Query

SELECT q_tab.c_year year, q_tab.loc_id,
 SUM(DECODE(q_tab.mon_cd,'JAN', q_tab.sales)) JAN,
 SUM(DECODE(q_tab.mon_cd,'FEB', q_tab.sales)) feb,
 SUM(DECODE(q_tab.mon_cd,'MAR', q_tab.sales)) mar,
 SUM(DECODE(q_tab.mon_cd,'APR', q_tab.sales)) apr,
 SUM(DECODE(q_tab.mon_cd,'MAY', q_tab.sales)) may,
 SUM(DECODE(q_tab.mon_cd,'JUN', q_tab.sales)) jun,
 SUM(DECODE(q_tab.mon_cd,'JUL', q_tab.sales)) jul,
 SUM(DECODE(q_tab.mon_cd,'AUG', q_tab.sales)) aug,
 SUM(DECODE(q_tab.mon_cd,'SEP', q_tab.sales)) sep,
 SUM(DECODE(q_tab.mon_cd,'OCT', q_tab.sales)) oct,
 SUM(DECODE(q_tab.mon_cd,'NOV', q_tab.sales)) nov,
 SUM(DECODE(q_tab.mon_cd,'DEC', q_tab.sales)) dec
FROM
 (SELECT  2013 c_year, 100 loc_id, 'JAN' mon_cd, 1234 sales FROm DUAL UNION
  SELECT  2013 c_year, 100 loc_id, 'FEB' mon_cd, 1012 sales FROm DUAL UNION
  SELECT  2013 c_year, 100 loc_id, 'MAR' mon_cd, NULL sales FROm DUAL UNION
  SELECT  2013 c_year, 100 loc_id, 'MAY' mon_cd, 900  sales FROm DUAL UNION
  SELECT  2013 c_year, 100 loc_id, 'AUG' mon_cd, 536  sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'JAN' mon_cd, 1984 sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'FEB' mon_cd, 65   sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'MAR' mon_cd, NULL sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'APR' mon_cd, 653  sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'JUN' mon_cd, 679  sales FROm DUAL UNION
  SELECT  2013 c_year, 200 loc_id, 'JUL' mon_cd, 1731 sales FROm DUAL UNION
  SELECT  2013 c_year, 300 loc_id, 'JAN' mon_cd, 2918 sales FROm DUAL UNION
  SELECT  2013 c_year, 300 loc_id, 'FEB' mon_cd, NULL sales FROm DUAL UNION
  SELECT  2013 c_year, 300 loc_id, 'MAR' mon_cd, 1711 sales FROm DUAL UNION
  SELECT  2013 c_year, 300 loc_id, 'MAY' mon_cd,    9 sales FROm DUAL UNION
  SELECT  2013 c_year, 300 loc_id, 'JUL' mon_cd, 3771 sales FROm DUAL
 ) q_tab
GROUP BY q_tab.c_year, q_tab.loc_id
ORDER BY q_tab.c_year, q_tab.loc_id;

Back

Oracle registered trademark of Oracle Corporation.

Last Revised On: 10/07/2013