Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #191913] Fri, 08 September 2006 10:52 Go to next message
shivaram9
Messages: 35
Registered: August 2006
Member
Hi all,

I need some help from you all for the below
data formatting.

I want to have just a SQL query

i get the below results from a query.

MONTH COUNT
MAY 12
JUN 1
JUL 27
AUG 39
SEP 1

i wanted to know if this could be formatted for
me to show:
Month May Jun Jul Aug Sep
count 12 1 27 39 1

Some help would be greatly appreciated.

Thanks
Re: SQL Query [message #191943 is a reply to message #191913] Fri, 08 September 2006 16:19 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
it's not a "formatting" issue. it's a SQL issue. you need to write the query to display the data across instead of down. it's called PIVOT. you can search for PIVOT here or at asktom and find an answer.

but, since there are a fixed number of months in a year (12), you don't need anything as fancy as a pivot. you can do this:

select key,
sum( decode( to_char(dt,'mm'), '01', val, 0 ) ) jan_data,
sum( decode( to_char(dt,'mm'), '02', val, 0 ) ) feb_data,
...
sum( decode( to_char(dt,'mm'), '12', val, 0 ) ) dec_data
from tab
group by key;

of course, if your table contains multiple years of data, you'll need to deal with that. maybe a where clause:
where dt between to_date('01-01-2005','dd-mm-yyyy')
and to_date('31-12-2005','dd-mm-yyyy')
Re: SQL Query [message #191944 is a reply to message #191913] Fri, 08 September 2006 16:55 Go to previous messageGo to next message
shivaram9
Messages: 35
Registered: August 2006
Member
It worked,

thanks alot.
Re: SQL Query [message #192149 is a reply to message #191944] Mon, 11 September 2006 04:47 Go to previous messageGo to next message
maruti
Messages: 2
Registered: September 2006
Location: bangalore
Junior Member
hi.....friend...
u can also try this.....
----------------------------

select to_char(HIREDATE,'mm'),
decode((to_char(HIREDATE,'mm')),01,'JAN',02,'FEB',04,'APR',05,'MAY',06,'jun',07,
'JUL',08,'AUG',09,'SEP',10,'OCT',11,'NOV',12,'DEC')
from emp group by to_char(HIREDATE,'mm')

output:
----------------
01 JAN
02 FEB
04 APR
05 MAY
06 jun
09 SEP
11 NOV
12 DEC

8 rows selected.

Thax.........
maruti.n.k
Re: SQL Query [message #192189 is a reply to message #192149] Mon, 11 September 2006 07:14 Go to previous message
shoblock
Messages: 325
Registered: April 2004
Senior Member
aside from the fact that your sql is NOT what the OP asked for, there's also the issue that you reinvented the TO_CHAR function. your decode can be replaced with:
to_char(hiredate,'MON')
Previous Topic: using a sequence in a view
Next Topic: SQL Time-out
Goto Forum:
  


Current Time: Mon Dec 05 03:05:11 CST 2016

Total time taken to generate the page: 0.09098 seconds