Oracle query assistence

From: <trpost_at_gmail.com>
Date: Tue, 13 Nov 2007 16:46:15 -0800
Message-ID: <1195001175.203767.42500@y27g2000pre.googlegroups.com>

I have a table defined as follows:

CREATE TABLE products
(

   report_date	DATE	         not null,
   product_name	varchar2(128)    not null,
   num_cases	number(4)        not null,

   CONSTRAINT peoplesoftProduct_pk PRIMARY KEY (report_date, product_name)
 );

Here is a query I am running that produces the data I am after:

SELECT PRODUCT_NAME, NUM_CASES,
to_char(REPORT_DATE,'Mon YYYY') AS "Report Date" FROM PRODUCTS
WHERE PRODUCT_NAME IN ('Product1', 'Product2') AND report_date < to_date ('2007-09-30','YYYY-MM-DD') AND report_date > to_date ('2007-09-30','YYYY-MM-DD') - 365 ORDER BY PRODUCT_NAME ASC, REPORT_DATE ASC Here is the output:

PRODUCT_NAME NUM_CASES Report Date

Product1	429	Nov 2006
Product1	238	Dec 2006
Product1	304	Jan 2007
Product1	284	Feb 2007
Product1	271	Mar 2007
Product1	352	Apr 2007
Product1	422	May 2007
Product1	242	Jun 2007
Product1	220	Jul 2007
Product1	211	Aug 2007
Product1	247	Sep 2007
Product2	176	Nov 2006
Product2	152	Dec 2006
Product2	157	Jan 2007
Product2	126	Feb 2007
Product2	139	Mar 2007
Product2	156	Apr 2007
Product2	206	May 2007
Product2	206	Jun 2007
Product2	262	Jul 2007
Product2	213	Aug 2007
Product2	187	Sep 2007

The way I actually want the data to display from a query is as follows:

	Nov 2006	                Dec 2006	                Jan
2007	                ...
Product1	429		238		304		...
Product2	176		152		157		...

Now I know I could use the above query and write a script to process the data to make it output as I wish, but I really want to do this in a query. Is this possible and how would I adapt the above query to do so?

Thanks! Received on Tue Nov 13 2007 - 18:46:15 CST