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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dias Costa 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).