RE: CROSS TAB QUERY

From: Aponte, Tony <AponteT_at_hsn.net>
Date: Thu, 29 Nov 2001 10:34:24 -0800
Message-ID: <F001.003D0D8E.20011129102324@fatcity.com>

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:moyam_at_mtn.co.ug]

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: moyam_at_mtn.co.ug


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: ListGuru_at_fatcity.com (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).

Received on Thu Nov 29 2001 - 12:34:24 CST