Re: a cross tab??

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 02 Jan 2008 19:49:53 -0800
Message-ID: <1199332172.687511@bubbleator.drizzle.com>


Totti wrote:
> hi all,
>
> i have a little problem with a formula in oracle which i dont know how
> to do it all, i have done many attempts but no success!
> i will write the result i am aiming to get here:
>
> Month Year SP-20.2 SP-20.1 SP-20.6
> ----- ---- ---------- ---------- ----------
> 01 1998 400 140 80
> 02 1998 0 0 0
> 03 1998 0 0 99
> 04 1998 0 163 0
> 05 1998 420 0 90
> 06 1998 0 0 0
> 07 1998 0 155 88
> 08 1998 0 151 85
> 09 1998 481 145 81
> 10 1998 0 0 0
> 11 1998 0 0 110
> 12 1998 0 0 0
> 01 1999 0 0 89
> 02 1999 456 163 86
> 03 1999 0 0 81
> 04 1999 0 0 0
> 05 1999 0 162 87
> 06 1999 413 0 90
> 07 1999 0 0 0
>
> the problem is that i dont know oracle very well, i am pretty new to
> it;
> the data is in 2 different tables, i did the following to get the 1st
> 2 columns from a table:
>
> SELECT TO_CHAR(Pmt_date,'mm') MONTH ,TO_CHAR(Pmt_date,'YYYY') YEAR
> from purpmt
> group by TO_CHAR(Pmt_date,'mm'),TO_CHAR(Pmt_date,'YYYY')
> order by TO_CHAR(Pmt_date,'YYYY')
>
> but how can i cross tabulate it with the other table to get the result
> as i want it?
> these are the total purchases per month for these 3 products. and only
> these 3 since they are the top most purchased.
> would any body please provide me a formula or something?
> thanks for any help

In 10g or before crosstabulate using DECODE or CASE Demos here: http://www.psoug.org/reference/decode_case.html

In 11g use PIVOT and UNPIVOT
Demos here: http://www.psoug.org/reference/ora_operators.html#oppv

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Jan 02 2008 - 21:49:53 CST

Original text of this message