Re: Oracle query assistence

On Nov 13, 7:21 pm, bdbafh <bdb..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

My fault... It is a Oracle 10G database, sorry I do not know the patch release.

Thanks Received on Tue Nov 13 2007 - 21:06:32 CST