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

Totti wrote:
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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

well, to do this, i am trying to use two functions, but as i
understood from the site Mr. Morgan sent me (thank him very much), the
crosstabulation must be done from one table; this is not the case with
me.the first two columns are generated so :

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')

and the rest like so:

select decode(to_char(Prod_Code),'SP-20.2', qty) as frstProd,
decode(to_char(Prod_Code),'SP-20.1', qty ) as scndProd,
decode(to_char(Prod_Code),'SP-20.6', qty ) as thirdProd
from purch

now how can i relate those two functions? to get the result in the
upper message and also i want to fill the gaps generated by the second
func. by '0', but i am not sure how to use the NVL to do so.

Thanks you all

On Jan 2, 5:36*pm, Totti <saliba.toufic.geo... (AT) gmail (DOT) com> wrote:
Why not try something like:

SELECT TO_CHAR(Pmt_date,'mm') MONTH ,TO_CHAR(Pmt_date,'YYYY') YEAR,
case when product = 'SP-20.2'
then sum(nvl(qty,0))
else
sum(0)
end SP-20.2,
case when product = 'SP-20.1'
then sum(nvl(qty,0))
else
sum(0)
end SP-20.1,
case when product = 'SP-20.6'
then sum(nvl(qty,0))
else
sum(0)
end SP-20.6
from purpmt, purch
where <however purpmt and purch are joined>
and product in ('SP-20.2', 'SP-20.1', 'SP-20.6')
group by TO_CHAR(Pmt_date,'mm') MONTH ,TO_CHAR(Pmt_date,'YYYY') YEAR,
product
order by TO_CHAR(Pmt_date,'mm') MONTH ,TO_CHAR(Pmt_date,'YYYY') YEAR