Re: how to collapse pivot query

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Sat, 24 Jul 2004 04:14:37 GMT
Message-ID: <Xns95301D07634Epobox002bebubcom@204.127.199.17>

"Mike Stenzler" <mstenzler_at_ssaris.com> wrote in news:Mq8Mc.4475$bN4.3774_at_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.
> ///////////////////////////////////////////////////////

> //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
Mike,

You are nearly there, you just have it backwards and so a bit more complicated than necessary.

SQL> select trade_date, sum (ad) ad, sum(ah) ah, sum(ba) ba   2 from (
  3 select trade_date,

  4     decode(cmdy, 'AD', pl, null) AD,
  5     decode(cmdy, 'AH', pl, null) AH,
  6     decode(cmdy, 'BA', pl, null) BA
  7    from theotest
  8     where sys_id in ('E','F','S')
  9     and cmdy in ('AD', 'AH', 'BA')
 10     and trade_date > to_date('01-01-2000','MM-DD-YYYY')
 11     and trade_date < to_date('01-10-2000','MM-DD-YYYY')

 12 )
 13 group by trade_date
 14 /

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

>

> Ideally, the pivot query shopuld be capable of dynamically assigning n
> columns,

>

See asktom for an example of doing this using PL/SQL

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:766825 833740#14642820392307

-- 
Hth
Martin

add one to po box for mail

Received on Fri Jul 23 2004 - 23:14:37 CDT