Re: a cross tab??

From: patrick <pgovern_at_u.washington.edu>
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