Re: Oracle query assistence

On Nov 13, 7:46 pm, trp..._at_gmail.com wrote:
> 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!

Great news!
There is a PIVOT operator in 11g.
Head over to OTN, download it and enjoy.

You didn't include the Oracle database server software version that you are running. Sometimes patchsets and operating systems are relevant info.

If you need to use a technique that is supported prior to 11g, post your version info and someone will supply a link to the appropriate posting over on the AskTom site.

hth.

-bdbafh Received on Tue Nov 13 2007 - 20:21:51 CST