how to collapse pivot query

From: Mike Stenzler <mstenzler_at_ssaris.com>
Date: Fri, 23 Jul 2004 09:31:23 -0400
Message-ID: <Mq8Mc.4475$bN4.3774@fe11.usenetserver.com>

I'm having difficulty in getting a pivot query to do what I want.

I've included the desired result where the data is grouped by trade_date and lined up in columnar form - each row represents a day's worth (sum(EFS) of pl per
column (cmdy)

//desired result of magic sql pivot query
///////////////////////////////////////////////////////

TRADE_DAT   AD           AH           BA
---------            ---------- ---------- ----------
03-JAN-00        760
04-JAN-00      -1200       -975          0
05-JAN-00        690        1275          0
06-JAN-00       -810        225           -51.46
07-JAN-00        120         600          -68.65

5 rows selected.
///////////////////////////////////////////////////////

Ideally, the pivot query shopuld be capable of dynamically assigning n columns,
but I've set out to solve the static version first. Worst case I can build this
query in it's static form (with n decode statements) from within a loop in python.
Of course, I'd like to figure out how to do it more elegantly, but for starters I'd
like to know how to do it as a static query. By static I mean that I know how
many columns I want in advance, so I know how many decode statements I'll have
and since I'll be using python I can build the sql text statement dynamically.

//sample table
///////////////////////////////////////////////////////
create table theotest
(
trade_date date,
cmdy varchar2(6),
sys_id varchar2(1),
pl number(16,2)
);
///////////////////////////////////////////////////////

sample data
///////////////////////////////////////////////////////
insert into theotest
values(to_date('01-03-2000','MM-DD-YYYY'),'AD','E',380.0); insert into theotest
values(to_date('01-03-2000','MM-DD-YYYY'),'AD','F',0.0); insert into theotest
values(to_date('01-03-2000','MM-DD-YYYY'),'AD','S',380.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AD','E',-600.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AD','F',0.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AD','S',-600.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AH','E',-325.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AH','F',-325.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'AH','S',-325.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'BA','E',0.0); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'BA','F',-103.48); insert into theotest
values(to_date('01-04-2000','MM-DD-YYYY'),'BA','S',103.48); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AD','E',230.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AD','F',230.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AD','S',230.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AH','E',425.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AH','F',425.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'AH','S',425.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'BA','E',0.0); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'BA','F',-189.38); insert into theotest
values(to_date('01-05-2000','MM-DD-YYYY'),'BA','S',189.38); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AD','E',-270.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AD','F',-270.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AD','S',-270.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AH','E',75.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AH','F',75.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'AH','S',75.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'BA','E',0.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'BA','F',0.0); insert into theotest
values(to_date('01-06-2000','MM-DD-YYYY'),'BA','S',-51.46); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AD','E',40.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AD','F',40.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AD','S',40.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AH','E',200.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AH','F',200.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'AH','S',200.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'BA','E',0.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'BA','F',0.0); insert into theotest
values(to_date('01-07-2000','MM-DD-YYYY'),'BA','S',-68.65); COMMIT;
///////////////////////////////////////////////////////

//my first static query attempt and result set.. getting there but need to
//collapse the dates
//////////////////////////////////////////////////////
select trade_date,

 decode(cmdy, 'AD', spl, null) AD,
 decode(cmdy, 'AH', spl, null) AH,
 decode(cmdy, 'BA', spl, null) BA,

from (
select trade_date, cmdy, sum(pl) spl
from theotest

   where sys_id in ('E','F','S')
   and cmdy in ('AD', 'AH', 'BA')
   and trade_date > to_date('01-01-2000','MM-DD-YYYY')
   and trade_date < to_date('01-10-2000','MM-DD-YYYY')

group by trade_date, cmdy
)
group by trade_date, cmdy, spl;

TRADE_DAT         AD          AH           BA
---------                 ---------- ---------- ---------- 
03-JAN-00            760
04-JAN-00           -1200
04-JAN-00                            -975
04-JAN-00                                             0
05-JAN-00            690
05-JAN-00                           1275
05-JAN-00                                            0
06-JAN-00           -810
06-JAN-00                            225
06-JAN-00                                          -51.46
07-JAN-00            120
07-JAN-00                           600
07-JAN-00                                         -68.65

13 rows selected.
//////////////////////////////////////////////////////

I'd appreciate any insight you can offer.

TIA Mike Received on Fri Jul 23 2004 - 08:31:23 CDT