Re: Can SQL*Plus do pivoting fuctions?

From: Cleekjf <cleekjf_at_aol.com>
Date: 1997/04/03
Message-ID: <19970403050400.AAA02571@ladder01.news.aol.com>#1/1

04/03/97

Hi Lim,

Yes, Oracle(SQL*PLUS) provides some really neat ways to basically turn a table on its side like you're wanting to do. You can simulate Excel's pivoting function. The 'decode' function is VERY powerful. Learn how to use it and you will amaze people, including yourself.

Here's what I did: I created a test table that has 20 rows for each mth listed below. On this table is a column for the date and one for the dollars.          

select sum(decode(cl_date,'121996',cl_adj_amt,0)) dec,

         sum(decode(cl_date,'011997',cl_adj_amt,0)) jan,
         sum(decode(cl_date,'021997',cl_adj_amt,0)) feb,
         sum(decode(cl_date,'031997',cl_adj_amt,0)) mar

from test_table;

       DEC        JAN        FEB        MAR


----------       ----------   ----------   ----------

    7540.3 14178.7 13610.5 200872.49

You can either hard-code your date values or use the add_months Oracle function to always get the current mth, the +1 month, the +2 month and so on.

Here's a sample using Oracle's "dual" database and how the add_months can be used:

select to_char(add_months(sysdate,0),'mmyyyy') now,

         to_char(add_months(sysdate,+1),'mmyyyy') now_plus1 from dual

NOW

---

NOW_PLUS1

---

041997
051997

Try to get your hands on a book called 'Oracle: The Complete Reference'. It's great!!

Jim Cleek
e-mail:czxq33_at_agt.gmeds.com Received on Thu Apr 03 1997 - 00:00:00 CST