Pivot query in oracle- Please help!!

From: longhorntx <kdjain_at_gmail.com>
Date: 17 Mar 2005 06:49:36 -0800
Message-ID: <1111070976.679741.240290@l41g2000cwc.googlegroups.com>

I have a table with following values

City	STATE	COLOR	REGION
city1	Texas	White	SW
city2	Texas	Gray	SW
city3	Texas	Gray	SW
city4	Nevada	Gray	PAC
c5	Texas	Yellow	SW
c6	Florida	Yellow	SE
c7	Missouri	Orange	W
c8	Texas	Orange	SW

I am trying to create a report which looks like:

STATE	White	Gray	Yellow	Orange
Texas	1	2	0	1
Nevada	0	1	0	0
Florida	0	0	1	0
Missouri	0	0	0	1

I need to do this in oracle pl/sql
I tried to write this query

select state,

SUM( decode(color,'Orange',count(state),0) ) as orange,
SUM( decode(color,'Yellow',count(state),0) ) as yellow,
SUM( decode(color,'Gray',count(state),0) ) as gray
SUM( decode(color,'White',count(state),0) ) as white

from my_table
group by color

This query gives me an error "ORA-00937: not a single-group group function"
Please help !
any ideas??? Received on Thu Mar 17 2005 - 08:49:36 CST