Re: Cross-tab queries in SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 09 Jun 1998 12:55:40 GMT
Message-ID: <357e300d.2118526@192.86.155.100>

A copy of this was sent to Tansel Ozkan <tansel_at_openix.com> (if that email address didn't require changing) On Tue, 09 Jun 1998 05:45:50 -0400, you wrote:

>Hello all,
>
>Is there any way to write cross-tab type queries in SQL?
>Such that the columns are variable?
>

Yes, if you know the entire domain of the column you want to pivot on. For example, if you have 4 product id's you can code:

select month,

       sum( decode( product_id, 1, revenue, 0 ) ) "Product 1",
       sum( decode( product_id, 2, revenue, 0 ) ) "Product 2",
       sum( decode( product_id, 3, revenue, 0 ) ) "Product 3",
       sum( decode( product_id, 4, revenue, 0 ) ) "Product 4"

  from test
 group by month
/

If you don't know the range of product ID's, you might consider pivoting on MONTH which as a discrete set of known values...

That would look similar:

select product_id,

       sum( decode( month, 'January', revenue, 0 ) ) "Jan",
       ...
       sum( decode( month, 'December', revenue, 0 ) ) "Dec"

  from test
 group by product_id
/

>
>For example:
>
>crate table test (
>month varchar2(20),
>product_id number,
>revenue number);
>
> Sum of Revenue
> Product Id
>Month 1 2 3 4
>January 140 320 300 340
>February .. .. .. ..
>March
>
>
>
>
>Thanks..
>
>Tansel
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jun 09 1998 - 07:55:40 CDT