select deptno, sum(jan_04) january, sum(feb_04) february, ....
from (
select deptno,
decode(to_char(hiredate,'mm/yyyy'),'01/2004,1,0) jan_04,
decode(to_char(hiredate,'mm/yyyy'),'02/2004,1,0) feb_04,
...
from emp
)
group by deptno

This example used absolute values. You can get the date headers dynamically by declaring a variable for the first month in the date range and then use the add_months function.

Aryeh Keefe


-----Original Message-----
From: Oracle DBA Help [mailto:[Email Address Removed]
Sent: ו 30/01/2004 18:17
To: LazyDBA.com Discussion
Cc:
Subject: creating a pivot result set



EMP
-------
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO


THis is basically the query
SELECT count(*), to_char(hiredate,'MON-YYYY'), DEPTNO
from emp
group by to_char(hiredate,'MON-YYYY'),DEPTNO

But I need the result set in a pivot table like this:

MONTH JAN-2003 FEB-2003 MAR-2003 APR-2003 .....JAN-2004 TOTAL
DEPTNO ------- -------- ------- -------- -------- -----
10 3 0 5 4 0 12
20 1 0 0 5 6 12
30 7 8 0 0 0 15
40 0 0 0 0 1 1
------ --- --- --- --- --- ---
TOTAL 11 8 5 9 7 40

I have no idea how to do this...can anyone help me here




**************************************************************************************************
This e-mail message has been sent by Team Computer and Systems Ltd. and is for the
use of the intended recipients only. This document may contain confidential,
commercial and privileged information or data that constitute proprietary information of
TEAM Computers & Systems. Any review or distribution by others is strictly prohibited.
If you are not the intended recipient you are hereby notified that any use of this
information or data by any other person is absolutely prohibited and your repuested to
delete the e-mail and notify the sender immediately. Thank You.
**************************************************************************************************

** eSafe scanned this email for viruses, vandals and malicious content **


Oracle LazyDBA home page