I gave it a whack and came up with the following solution (I needed the distraction). For demo purposes each statement build upon the previous ones. But first a little reality. There are some problem boundaries that can be declared. The first one is that there are 12 months in the year so we can adjust the SQL to accommodate them. The second is that the table's columns are also known.
I created a bogus table with the format you included in the example:
create table x (MONTH VARCHAR2(20),
ERLANG NUMBER(7,2),
CONG NUMBER(5,2),
CSETUP NUMBER(5,2));
insert into x values('JAN',1,1,1);
insert into x values('FEB',2,2,2);
insert into x values('MAR',3,3,3);
insert into x values('DEC',4,4,4);
commit;
Since the table's columns are known I created an in-line view of month,column name,value:
SELECT month,colname,value
FROM (SELECT month,'erlang' colname,erlang value from x
UNION
SELECT month,'cong',cong from x
UNION
SELECT month,'csetup',csetup from x);
With this statement I add a row number to each so that I can pivot by them:
SELECT month,colname,value,row_number() over(partition by colname order by month nulls last)
FROM (SELECT month,'erlang' colname,erlang value from x
UNION
SELECT month,'cong',cong from x
UNION
SELECT month,'csetup',csetup from x);
By adding the DECODE I can place the months into columns:
SELECT
colname
,DECODE(line_no,01,month,NULL) Month01
,DECODE(line_no,02,month,NULL) Month02
,DECODE(line_no,03,month,NULL) Month03
,DECODE(line_no,04,month,NULL) Month04
,DECODE(line_no,05,month,NULL) Month05
,DECODE(line_no,06,month,NULL) Month06
,DECODE(line_no,07,month,NULL) Month07
,DECODE(line_no,08,month,NULL) Month08
,DECODE(line_no,09,month,NULL) Month09
,DECODE(line_no,10,month,NULL) Month10
,DECODE(line_no,11,month,NULL) Month11
,DECODE(line_no,12,month,NULL) Month12
FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no
FROM (SELECT month,'erlang' colname,erlang value from x
UNION
SELECT month,'cong',cong from x
UNION
SELECT month,'csetup',csetup from x)
);
The resultset still needs work to put it into a single row. Here I use the MAX/GROUP functions to return a single row of month names to be used as the column heading of your report:
SELECT
colname
,MAX(DECODE(line_no,01,month,NULL)) Month01
,MAX(DECODE(line_no,02,month,NULL)) Month02
,MAX(DECODE(line_no,03,month,NULL)) Month03
,MAX(DECODE(line_no,04,month,NULL)) Month04
,MAX(DECODE(line_no,05,month,NULL)) Month05
,MAX(DECODE(line_no,06,month,NULL)) Month06
,MAX(DECODE(line_no,07,month,NULL)) Month07
,MAX(DECODE(line_no,08,month,NULL)) Month08
,MAX(DECODE(line_no,09,month,NULL)) Month09
,MAX(DECODE(line_no,10,month,NULL)) Month10
,MAX(DECODE(line_no,11,month,NULL)) Month11
,MAX(DECODE(line_no,12,month,NULL)) Month12
FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no
FROM (SELECT month,'erlang' colname,erlang value from x
UNION
SELECT month,'cong',cong from x
UNION
SELECT month,'csetup',csetup from x)
)
GROUP BY colname;
Now change the column from month to value to get the actual row data that used to be in columns:
SELECT
colname
,MAX(DECODE(line_no,01,value,NULL)) value01
,MAX(DECODE(line_no,02,value,NULL)) value02
,MAX(DECODE(line_no,03,value,NULL)) value03
,MAX(DECODE(line_no,04,value,NULL)) value04
,MAX(DECODE(line_no,05,value,NULL)) value05
,MAX(DECODE(line_no,06,value,NULL)) value06
,MAX(DECODE(line_no,07,value,NULL)) value07
,MAX(DECODE(line_no,08,value,NULL)) value08
,MAX(DECODE(line_no,09,value,NULL)) value09
,MAX(DECODE(line_no,10,value,NULL)) value10
,MAX(DECODE(line_no,11,value,NULL)) value11
,MAX(DECODE(line_no,12,value,NULL)) value12
FROM (SELECT month,colname,value,row_number() over(partition by colname order by month nulls last) line_no
FROM (SELECT month,'erlang' colname,erlang value from x
UNION
SELECT month,'cong',cong from x
UNION
SELECT month,'csetup',csetup from x)
)
GROUP BY colname;
I didn't handle sorting by calendar month since that's easy enough to handle with DECODES, but it would have distracted from the gist.
HTH
Tont Aponte
-----Original Message-----
From: Moses Ngati Moya [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 28, 2001 3:15 AM
To: Multiple recipients of list ORACLE-L
Subject: CROSS TAB QUERY
Hi Gurus,
I have a table Monthly_Stats as below:
MONTH VARCHAR2(20),
ERLANG NUMBER(7,2)
CONG NUMBER(5,2)
.
.
CSETUP NUMBER(5,2)
A record is inserted in this table every end of month.
I would like to write an SQL query to produce output below:
MONTH
JAN FEB MAR APR MAY . . .
DEC
ERLANG 777 999 98 66 87 999
CONG 9 6 3 4 2
4
CSETUP
.
.
.
I do not know the number of months in advance, i.e. if the table has 3 months
(JAN, FEB, MAR), these are the only months I need in the report.
Any suggestions??
Moses Moya Ngati
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Moses Ngati Moya
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
