I have data in a table as seen below:
MONTH VALUE
1 100
2 200
3 300
4 400
5 500
6 600
I want to write a SQL query so that result is given as below:
MONTH_JAN MONTH_FEB MONTH_MAR MONTH_APR MONTH_MAY MONTH_JUN
100 200 300 400 500 600
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)
);
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 REFCURSOR
EXEC :rc := Get_Month_Values;
PRINT rc
from SQL Developer's command line
how to get pivot of the table
SELECT *
FROM Product
PIVOT (MAX(VALUE) FOR (P_name, P_id) IN ((choclate,6) AS choclate_6, (candies,8) AS candies_8, (Biscuits,9) AS Biscuits_9)
