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