Oracle SQL pivot query

Oracle 9i+ supports:

SELECT SUM(CASE WHEN t.month = 1 THEN t.value ELSE 0 END) AS JAN,
       SUM(CASE WHEN t.month = 2 THEN t.value ELSE 0 END) AS FEB,
       SUM(CASE WHEN t.month = 3 THEN t.value ELSE 0 END) AS MAR,
       SUM(CASE WHEN t.month = 4 THEN t.value ELSE 0 END) AS APR,
       SUM(CASE WHEN t.month = 5 THEN t.value ELSE 0 END) AS MAY,
       SUM(CASE WHEN t.month = 6 THEN t.value ELSE 0 END) AS JUN
  FROM YOUR_TABLE t

You only list two columns -- something like this should probably be grouped by year.

There is ANSI PIVOT (and UNPIVOT) syntax, but Oracle didn't support it until 11g. Prior to 9i, you'd have to replace the CASE statements with Oracle specific DECODE.

Oracle 11g and above

As of Oracle 11g, you can now use the PIVOT operator to achieve that result:

create table tq84_pivot (
  month number,
  value number
);

insert into tq84_pivot values(1, 100);
insert into tq84_pivot values(2, 200);
insert into tq84_pivot values(3, 300);
insert into tq84_pivot values(4, 400);
insert into tq84_pivot values(5, 500);
insert into tq84_pivot values(6, 600);
--
insert into tq84_pivot values(1, 400);
insert into tq84_pivot values(2, 350);
insert into tq84_pivot values(4, 150);

select 
  *
from
  tq84_pivot
pivot (
   sum (value) as sum_value for
     (month) in (1 as month_jan,
                 2 as month_feb,
                 3 as month_mar,
                 4 as month_apr,
                 5 as month_mai,
                 6 as month_jun,
                 7 as month_jul,
                 8 as month_aug,
                 9 as month_sep,
                10 as month_oct,
                11 as month_nov,
                12 as month_dec)
);

Dynamic Pivot for Oracle 11g+

There’s no straightforward method for dynamic pivoting in Oracle’s SQL, unless it returns XML type results.

For the non-XML results PL/SQL might be used through creating functions of SYS_REFCURSOR return type

• With PIVOT Clause

  CREATE OR REPLACE FUNCTION Get_Month_Values RETURN SYS_REFCURSOR IS
     v_recordset SYS_REFCURSOR;
     v_sql       VARCHAR2(32767);
     v_cols      VARCHAR2(32767);
  BEGIN
     SELECT LISTAGG( ''''||month||''' AS "MONTH_'||TO_CHAR( TO_DATE(month,'mm') ,'MON')||'"' , ',' )
                    WITHIN GROUP ( ORDER BY month )
       INTO v_cols
       FROM tab;
  
     v_sql :='SELECT *
                FROM tab t
               PIVOT
               (
                MAX(value) FOR month IN ( '|| v_cols ||' )
               )';
  
     OPEN v_recordset FOR v_sql;
     DBMS_OUTPUT.PUT_LINE(v_sql);
     RETURN v_recordset;
  END;
  /
  

• With Conditional Aggregation

  CREATE OR REPLACE FUNCTION Get_Month_Values RETURN SYS_REFCURSOR IS
     v_recordset SYS_REFCURSOR;
     v_sql       VARCHAR2(32767);
     v_cols      VARCHAR2(32767);
  BEGIN
     SELECT LISTAGG('MAX( CASE WHEN month = '''||month||''' THEN '||value||' END ) AS "MONTH_'||TO_CHAR( TO_DATE(month,'mm') ,'MON')||'"' , ',' )
                    WITHIN GROUP ( ORDER BY month )                 
       INTO v_cols
       FROM tab;
  
     v_sql :='SELECT '|| v_cols ||' FROM tab';
  
     OPEN v_recordset FOR v_sql;
     DBMS_OUTPUT.PUT_LINE(v_sql);
     RETURN v_recordset;
  END;
  /
  

and then the function can be invoked as

VAR rc REFCURSOR
EXEC :rc := Get_Month_Values;
PRINT rc

from SQL Developer's command line

Demo