Kayshav.com
About Developer Oracle 11g Technology Information Sitemap

Oracle UNPIVOT Function
-- Pivoting table data

WITH q_tab AS
 (SELECT 2012 c_year, 100 q1,  110 q2,  NULL q3, 110 q4 FROM DUAL UNION 
  SELECT 2013 c_year, 105 q1, NULL q2,   115 q3, 120 q4 FROM DUAL UNION
  SELECT 2014 c_year, 100 q1,  110 q2,   135 q3, 110 q4 FROM DUAL)
SELECT * FROM q_tab;

Oracle 11g PIVOT output

-- Unpivoting (normalizing) the data using 11g UNPIVOT function
-- To inlcude null values in the output, the 
-- INCLUDE NULLS option is needed as shown in query below.
-- NULL value of quantity (alias) in 2012 quarter 3 (q3) 
-- and 2013 quarter 2 (q2).

WITH q_tab AS
 (SELECT 2012 c_year, 100 q1,  110 q2,  NULL q3, 110 q4 FROM DUAL UNION
  SELECT 2013 c_year, 105 q1, NULL q2,   115 q3, 120 q4 FROM DUAL UNION
  SELECT 2014 c_year, 100 q1,  110 q2,   135 q3, 110 q4 FROM DUAL)
SELECT * FROM  q_tab
UNPIVOT INCLUDE NULLS 
 (quantity FOR qtr IN (q1 as 1, q2 as 2, q3 as 3, q4 as 4)
);

Oracle 11g UNPIVOT INCLUDE NULLS output

Oracle 11gXEr2 - Index

Oracle registered trademark of Oracle Corporation.

Last Revised On: September 21, 2014

  39896