By code990


2011-01-30 07:29:41 8 Comments

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

2 comments

@René Nyffenegger 2011-01-30 08:04:29

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

@OMG Ponies 2011-01-30 07:47:37

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.

Related Questions

Sponsored Content

22 Answered Questions

[SOLVED] Retrieving the last record in each group - MySQL

25 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2242540 View
  • 1524 Score
  • 25 Answer
  • Tags:   sql duplicates

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

30 Answered Questions

[SOLVED] SQL select only rows with max value on a column

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

32 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

15 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

37 Answered Questions

9 Answered Questions

[SOLVED] SQL update query using joins

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2002927 View
  • 1001 Score
  • 19 Answer
  • Tags:   sql oracle

Sponsored Content