Re: Syntax for simple (?) pivot with MODEL clause

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 02 Aug 2007 11:21:46 -0700
Message-ID: <1186078904.35505@bubbleator.drizzle.com>

dba_222_at_yahoo.com wrote:
> Dear Experts,
>
> I'm looking at the MODEL clause in 10G, and reading a number
> of articles on it.
>
> All I want to do is a simple pivot, with no hardcoding
> of potential values. Like what you can with Excel now.
>
> None of the articles I've read show any clear examples
> how to do this.
>
>
> For instance, looking at the SH schema:
>
> sh_at_dwhtest1>describe sales
> Name Null? Type
> ----------------------------------------------------- --------
> ---------------
> PROD_ID NOT NULL NUMBER
> CUST_ID NOT NULL NUMBER
> TIME_ID NOT NULL DATE
> CHANNEL_ID NOT NULL NUMBER
> PROMO_ID NOT NULL NUMBER
> QUANTITY_SOLD NOT NULL
> NUMBER(10,2)
> AMOUNT_SOLD NOT NULL
> NUMBER(10,2)
>
>
> Select channel_id, count(*)

>>From sales

> Group by channel_id
> order by 1
>
> CHANNEL_ID COUNT(*)
> ---------- ----------
> 2 258025
> 3 540328
> 4 118416
> 9 2074
>
> 4 rows selected.
>
>
> Select promo_id, count(*)

>>From sales

> Group by promo_id
> order by 1
>
> PROMO_ID COUNT(*)
> ---------- ----------
> 33 2074
> 350 18022
> 351 10910
> 999 887837
>
> 4 rows selected.
>
>
> Select channel_id,
> promo_id,
> count(*)

>>From sales


> Group by channel_id,

> 	promo_id

> order by 1
>
>
> CHANNEL_ID PROMO_ID COUNT(*)
> ---------- ---------- ----------
> 2 350 4746
> 2 999 253279
> 3 350 11310
> 3 351 10892
> 3 999 518126
> 4 350 1966
> 4 351 18
> 4 999 116432
> 9 33 2074
>
> 9 rows selected.
>
>
> I would like to get:
>
> Channel/ Promo ->
>
> 33 350 351 999
> 2
> 3
> 4
> 9
>
>
>
> With either an aggregate such as count(*), sum(QUANTITY_SOLD),
> or a sum(AMOUNT_SOLD ), as the value. ie.
>
>
> Channel/ Promo ->
>
> Channel 33 350 351 999
>
> 2 null 4746 null 253279
> 3 null 11310 10892 518126
> 4 null 1966 18 116432
> 9 2074 null null null
>
>
>
> So, if the data were to suddenly give me 100 distinct Promo values,
> the query would immediately return 100 columns. That is, with no
> modifications to the query.
>
> And hopefully, the values for the PROMO_ID that you find in the GROUP
> BY,
> (33, 350, 351, 999) would become the names of the columns returned,
> without hardcoding them.
>
>
> Currently, everything I'm looking seems to require that you already
> know
> the exact number of potential values, with a separate entry for
> each column. And, you code the name of each column.
>
>
> Is it possible to do this with Oracle's features?
>
> If so, What is the syntax to accomplish this?
>
>
> Thanks a lot!

Then use DECODE and don't post to every usenet group you can spell.

You will find demo code you can use to learn it in Morgan's Library at www.psoug.org. Scroll down to DECODE. You can also do the same thing using CASE.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Aug 02 2007 - 13:21:46 CDT