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
Merge rows into columns (PIVOT) Oracle Database 11g introduced the pivot operator. This makes switching rows to columns easy.
Example. SELECT * FROM (SELECT SUBSTR(time_id,06,02) AS month_val, prod_id, amount_sold FROM sales WHERE SUBSTR(time_id,01,04) = 1998 ) PIVOT ( SUM(amount_sold) FOR (month_val, prod_id) IN ((01,12008), (02, 12010) ,(03, 12011 ) ));
Oracle PIVOT with subqueryWhen you use a subquery in the pivot_in_clause , Oracle uses all values returned by the subquery for pivoting. Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With