Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle 11g Pivot
-- In earlier versions of Oracle DECODE(mon_cd,'JAN', sales) had to be used to get 
-- JAN_Sales, DECODE(mon_cd,'FEB', sales) for FEB_Sales ans so on.  Other ways to 
-- create crosstab query are is by use of dynamic SQL or SQL Model Clause.

-- Oracle 11g has a Pivot() function create crosstab query as shown in query below

WITH q_tab AS
 (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_mth AS
  (SELECT level mth_no, 2013 c_year,
    DECODE(level,1,'JAN',2,'FEB',3,'MAR',4,'APR', 5,'MAY',6,'JUN',
      7,'JUL',8,'AUG',9,'SEP',10,'OCT',11,'NOV','DEC') mth
   FROM DUAL CONNECT BY level<=12),
 q_final AS
  (SELECT  qm.c_year, qt.loc_id, qm.mth, qt.sales
   FROM q_tab qt, 
        q_mth qm
   WHERE qm.mth = qt.mon_cd (+)
   AND   qm.c_year = qt.c_year (+)
  )
SELECT *
FROM (SELECT  c_year, mth, loc_id, sales FROM q_final)
PIVOT (SUM(sales) sale
       FOR (mth) IN ('JAN' AS JAN,'FEB' AS FEB,'MAR' AS MAR,'APR' AS APR,
            'MAY' AS MAY, 'JUN' AS JUN))
WHERE loc_id IS NOT NULL
ORDER BY c_year, loc_id;

-- Note: Just six month data (Jan - Jun) shown in Pivot query

Oracle 11g Pivot Query


Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 21, 2014

  39894