Oracle SQL pivot query

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_pivotpivot (   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));

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.

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 REFCURSOREXEC :rc := Get_Month_Values;PRINT rc

from SQL Developer's command line

Demo