Hi !
May this complete example can help you:
- File: Cross_Tab.sql
- --------------------
--
--
#
set termout on
--
drop table Sales;
--
create table Sales
(Product varchar2(30),
Value number(8),
Invoice_Date date);
--
--
- insert some data for testing
--
alter session set nls_date_format='YYYY-MON-DD'; --
just to be sure for the proper date format
--
insert into Sales values ('PAPEL C0NTINUO', 117,
'2001-JAN-01');
insert into Sales values ('TINTA IMPRESSORA', 234,
'2001-JAN-01');
insert into Sales values ('BANDAS SIMPLES', 468,
'2001-JAN-01');
insert into Sales values ('DISQUETTES AMARELAS', 702,
'2001-JAN-01');
insert into Sales values ('TINTA FOTOC0PIADORA', 819,
'2001-JAN-01');
insert into Sales values ('CERVEJA LAGER', 1053,
'2001-JAN-01');
insert into Sales values ('SAIAS DE LA', 117,
'2001-FEB-11');
insert into Sales values ('SAIAS DE SEDA', 117,
'2001-FEB-11');
insert into Sales values ('CAMISAS DE LA', 117,
'2001-FEB-11');
insert into Sales values ('CAMISAS DE SEDA', 117,
'2001-FEB-11');
insert into Sales values ('SAIAS DE ALGODAO', 117,
'2001-FEB-11');
insert into Sales values ('PIJAMAS DE SEDA', 117,
'2001-FEB-11');
insert into Sales values ('PIJAMAS DE ALGODAO', 11.7,
'2001-APR-21');
insert into Sales values ('PIJAMAS DE SEDA', 58.5,
'2001-APR-21');
insert into Sales values ('SAIAS DE SEDA', 234,
'2001-APR-21');
insert into Sales values ('PIJAMAS DE ALGODAO', 11.7,
'2001-APR-21');
insert into Sales values ('PIJAMAS DE SEDA', 58.5,
'2001-APR-21');
insert into Sales values ('SAIAS DE SEDA', 234,
'2001-APR-21');
insert into Sales values ('SAIAS DE SEDA', 117,
'2001-JUN-01');
insert into Sales values ('SAIAS DE ALGODAO', 117,
'2001-JUN-01');
insert into Sales values ('CAMISETES DE ALGODAO', 117,
'2001-JUN-01');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-JUN-01');
insert into Sales values ('PIJAMAS DE SEDA', 117,
'2001-JUN-01');
insert into Sales values ('CAMISOLAS DE LA', 117,
'2001-JUN-01');
insert into Sales values ('SAIAS DE ALGODAO', 117,
'2001-MAY-05');
insert into Sales values ('SAIAS DE SEDA', 234,
'2001-MAY-05');
insert into Sales values ('PIJAMAS DE SEDA', 58.5,
'2001-MAY-05');
insert into Sales values ('SAIAS DE ALGODAO', 117,
'2001-MAY-05');
insert into Sales values ('SAIAS DE SEDA', 234,
'2001-MAY-05');
insert into Sales values ('PIJAMAS DE SEDA', 58.5,
'2001-MAY-05');
insert into Sales values ('PIJAMAS DE LA', 702,
'2001-JAN-01');
insert into Sales values ('PIJAMAS DE SEDA', 351,
'2001-JAN-01');
insert into Sales values ('CAMISOLAS DE LA', 936,
'2001-JAN-01');
insert into Sales values ('SAIAS DE ALGODAO', 1053,
'2001-JAN-01');
insert into Sales values ('SAIAS DE SEDA', 2574,
'2001-JUL-11');
insert into Sales values ('PIJAMAS DE SEDA', 643.5,
'2001-JUL-11');
insert into Sales values ('SAIAS DE ALGODAO', 1755,
'2001-JUL-11');
insert into Sales values ('SAIAS DE SEDA', 3978,
'2001-JUL-11');
insert into Sales values ('PIJAMAS DE SEDA', 1111.5,
'2001-JUL-11');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-FEB-21');
insert into Sales values ('PIJAMAS DE SEDA', 2457,
'2001-FEB-21');
insert into Sales values ('CAMISOLAS DE LA', 1404,
'2001-FEB-21');
insert into Sales values ('SAIAS DE ALGODAO', 3627,
'2001-FEB-21');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-FEB-21');
insert into Sales values ('PIJAMAS DE SEDA', 2398.5,
'2001-AUG-01');
insert into Sales values ('SAIAS DE ALGODAO', 14391,
'2001-AUG-01');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-AUG-01');
insert into Sales values ('PIJAMAS DE SEDA', 1111.5,
'2001-AUG-01');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-MAY-05');
insert into Sales values ('PIJAMAS DE SEDA', 2457,
'2001-MAY-05');
insert into Sales values ('CAMISOLAS DE LA', 2106,
'2001-MAY-05');
insert into Sales values ('SAIAS DE ALGODAO', 4797,
'2001-MAY-05');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-MAY-05');
insert into Sales values ('PIJAMAS DE SEDA', 2398.5,
'2001-JAN-01');
insert into Sales values ('SAIAS DE ALGODAO', 37791,
'2001-JAN-01');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-JAN-01');
insert into Sales values ('PIJAMAS DE SEDA', 1111.5,
'2001-JAN-01');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-SEP-11');
insert into Sales values ('PIJAMAS DE SEDA', 2457,
'2001-SEP-11');
insert into Sales values ('CAMISOLAS DE LA', 2106,
'2001-SEP-11');
insert into Sales values ('SAIAS DE ALGODAO', 4797,
'2001-SEP-11');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-SEP-11');
insert into Sales values ('PIJAMAS DE SEDA', 2398.5,
'2001-FEB-21');
insert into Sales values ('SAIAS DE ALGODAO', 37791,
'2001-FEB-21');
insert into Sales values ('SAIAS DE SEDA', 3042,
'2001-FEB-21');
insert into Sales values ('PIJAMAS DE SEDA', 1111.5,
'2001-FEB-21');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-MAR-01');
insert into Sales values ('PIJAMAS DE SEDA', 2457,
'2001-MAR-01');
insert into Sales values ('CAMISOLAS DE LA', 3276,
'2001-MAR-01');
insert into Sales values ('SAIAS DE ALGODAO', 4797,
'2001-MAR-01');
insert into Sales values ('SAIAS DE SEDA', 4212,
'2001-MAR-01');
insert into Sales values ('PIJAMAS DE SEDA', 643.5,
'2001-OCT-05');
insert into Sales values ('SAIAS DE ALGODAO', 2457,
'2001-OCT-05');
insert into Sales values ('SAIAS DE SEDA', 702,
'2001-OCT-05');
insert into Sales values ('PIJAMAS DE SEDA', 526.5,
'2001-OCT-05');
insert into Sales values ('PIJAMAS DE LA', 117,
'2001-NOV-05');
insert into Sales values ('PIJAMAS DE SEDA', 1597.05,
'2001-NOV-05');
insert into Sales values ('CAMISOLAS DE LA', 3276,
'2001-NOV-05');
insert into Sales values ('SAIAS DE ALGODAO', 2158.65,
'2001-NOV-05');
insert into Sales values ('SAIAS DE SEDA', 2084.94,
'2001-NOV-05');
insert into Sales values ('PIJAMAS DE SEDA', 386.1,
'2001-DEC-05');
insert into Sales values ('SAIAS DE ALGODAO', 1474.2,
'2001-DEC-05');
insert into Sales values ('SAIAS DE SEDA', 231.66,
'2001-DEC-05');
insert into Sales values ('PIJAMAS DE SEDA', 473.85,
'2001-DEC-05');
insert into Sales values ('CERVEJA LAGER', 117,
'2001-JAN-01');
--
--
drop table Months;
--
create table Months
(Month varchar2(3),
Month1 numeric(2),
Month2 numeric(2),
Month3 numeric(2),
Month4 numeric(2),
Month5 numeric(2),
Month6 numeric(2),
Month7 numeric(2),
Month8 numeric(2),
Month9 numeric(2),
Month10 numeric(2),
Month11 numeric(2),
Month12 numeric(2));
--
--
insert into Months values('JAN',1,0,0,0,0,0,0,0,0,0,0,0);
insert into Months values('FEB',0,1,0,0,0,0,0,0,0,0,0,0);
insert into Months values('MAR',0,0,1,0,0,0,0,0,0,0,0,0);
insert into Months values('APR',0,0,0,1,0,0,0,0,0,0,0,0);
insert into Months values('MAY',0,0,0,0,1,0,0,0,0,0,0,0);
insert into Months values('JUN',0,0,0,0,0,1,0,0,0,0,0,0);
insert into Months values('JUL',0,0,0,0,0,0,1,0,0,0,0,0);
insert into Months values('AUG',0,0,0,0,0,0,0,1,0,0,0,0);
insert into Months values('SEP',0,0,0,0,0,0,0,0,1,0,0,0);
insert into Months values('OCT',0,0,0,0,0,0,0,0,0,1,0,0);
insert into Months values('NOV',0,0,0,0,0,0,0,0,0,0,1,0);
insert into Months values('DEC',0,0,0,0,0,0,0,0,0,0,0,1);
--
--
- just to see what's in the Sales table
--
select Product, substr(to_char(Invoice_Date),4,3) as
Month,
sum(Value) as Sales_Value
from Sales
group by Product, Invoice_Date
/
--
- Finaly testing ...
--
set linesize 200
set pagesize 24
column Product format a20
--
select S.Product as Product,
sum(S.Value * M.Month1) as January,
sum(S.Value * M.Month2) as February,
sum(S.Value * M.Month3) as Mars,
sum(S.Value * M.Month4) as April,
sum(S.Value * M.Month5) as May,
sum(S.Value * M.Month6) as June,
sum(S.Value * M.Month7) as July,
sum(S.Value * M.Month8) as August,
sum(S.Value * M.Month9) as September,
sum(S.Value * M.Month10) as October,
sum(S.Value * M.Month11) as November,
sum(S.Value * M.Month12) as December
from Sales S, Months M
where substr(to_char(S.Invoice_Date),6,3) = M.Month
group by S.Product;
--
--
If you neeed some more help, I'll be glad to help You.
Bye
Dias Costa
Moses Ngati Moya wrote:
>
> 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).
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
--
Author: Dias Costa
INET: dcosta_at_lnec.pt
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 Wed Nov 28 2001 - 04:54:14 CST
