Re: Pivots in Oracle

You need to use the decode function

Given a table like this:

SQL> desc salessum

 Name                            Null?    Type
 ------------------------------- -------- ----
 CUSTNO                                   NUMBER(5)
 MONTHNO                                  NUMBER(2)
 SALES                                    NUMBER(8,2)

...do something like:

select custno,

       sum(decode(monthno,1,sales,0)) as JAN,
       sum(decode(monthno,2,sales,0)) as FEB,
       sum(decode(monthno,3,sales,0)) as MAR,
       sum(decode(monthno,4,sales,0)) as APR,
       sum(decode(monthno,5,sales,0)) as MAY,
       sum(decode(monthno,6,sales,0)) as JUN,
       sum(decode(monthno,7,sales,0)) as JUL,
       sum(decode(monthno,8,sales,0)) as AUG,
       sum(decode(monthno,9,sales,0)) as SEP,
       sum(decode(monthno,10,sales,0)) as OCT,
       sum(decode(monthno,11,sales,0)) as NOV,
       sum(decode(monthno,12,sales,0)) as DEC

CUSTNO   JAN   FEB   MAR   APR   MAY   JUN   JUL   AUG   SEP   OCT...
------ ----- ----- ----- ----- ----- ----- ----- ----- ----- -----...
     1  1000  2000   300   400     0     0     0     0     0     0...

HTH. Tom Sullivan

In article <39F3C2FD.377B1B01_at_erols.com>,   luhbey_at_erols.com wrote:
> I'm looking for stategies for multi-joining a single table in Oracle.
 I
> imagine there must be some. Here is what I need to do:
>
> I have a table that holds financial records stored on a monthly basis.
> For a given set of criteria including year, I need to
> return a set of records that descrbe the financial activity over that
> year, eg
>
> Criteria... Jan Feb Mar April May etc.
>
> The approach I'm following runs rather sluggish, eg:
> With a SQL statement I instantiate 12 copies of the table with each
 copy
> bringing back the particular month of interest,
> stringing all 12 together for the complete yearly view of each
> record.horizontally.
>
> Are there better ways to do this, or should I sharpen up my
 performance
> tuning skills?
>
> I'm running Oracle 8i.
>
> Thanks,
> Dave Godbey
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Oct 25 2000 - 16:05:57 CEST