Re: Transposition of data, SQL enigma
Date: 1996/10/08
Message-ID: <325adb4f.30979876_at_dcsun4>#1/1
On 8 Oct 1996 20:19:58 GMT, aje_at_lys.vnet.net (Andrew Joseph Esposito) wrote:
>Andrew Joseph Esposito (aje_at_vnet.net) wrote:
>: I have a table laid out in the following (simplified) fashion:
>: [...]
>: If you know, a quick note to aje_at_vnet.net would be extremely appreciated!
>: I'm working at First Union National Bank right now and could really use
>: some help.
>
>I thought I'd post the answer for anyone who was wondering how such a thing
>is done ... it hit me like a freight train once I calmed down.
>
>It's actually just joining the table to itself, several times...
>
>SELECT m1.acctnum as a, m1.abal as abal9505, m2.abal as abal9506,
> m3.abal as abal9507, ...
>FROM monthly m1, monthly m2, monthly m3, ...
>WHERE m1.yr_mo = '9505' AND m2.yr_mo = '9506' AND m3.yr_mo = '9507' AND ...
> AND m1.acctnum = m2.acctnum AND m2.acctnum = m3.acctnum AND m3.acctnum = ...
>;
>
a possibly faster, less resource intensive way is via decode as such:
select m1.acctnum a,
sum( decode( m1.yr_no, '9505', m1.abal, 0 ) ) aba19505,
sum( decode( m1.yr_no, '9056', m1.abal, 0 ) ) aba19506,
.....
from monthly ml
group by m1.acctnum
/
>Thanks for reading these anyway.
>
>Have a GREAT DAY!
>--
>Andrew Joseph Esposito
>aje_at_vnet.net
>http://www.vnet.net/users/aje
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
- Check out Oracle Governments web site! -----
Follow the link to "Tech Center"
and then downloadable Utilities for some free software...
statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Tue Oct 08 1996 - 00:00:00 CEST