Pivot query in oracle- Please help!!
From: longhorntx <kdjain_at_gmail.com>
Date: 17 Mar 2005 06:49:45 -0800
Message-ID: <1111070985.350522.272980_at_o13g2000cwo.googlegroups.com>
I have a table with following values
group by color
Date: 17 Mar 2005 06:49:45 -0800
Message-ID: <1111070985.350522.272980_at_o13g2000cwo.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 whitefrom 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 - 15:49:45 CET