Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: CROSS TAB QUERY

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US