Re: a cross tab??
Date: Thu, 3 Jan 2008 10:41:20 -0800 (PST)
Message-ID: <a9d2517d-9e43-44da-bf5d-3a5b3c7148df@e10g2000prf.googlegroups.com>
On Jan 2, 5:36 pm, Totti <saliba.toufic.geo..._at_gmail.com> 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
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 Received on Thu Jan 03 2008 - 12:41:20 CST