| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> pivot query question
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
)
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 can't leave out cmdy or spl from the final group by clause or I get the following error:
decode(cmdy, 'AD', spl, null) AD,
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
I'd appreciate any insight you can offer.
TIA Mike Received on Fri Jul 23 2004 - 10:04:09 CDT
 |
 |