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@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!

dba_222@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@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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

OK, I'm back. No emergencies today.

Our data changes on a daily, weekly and monthly basis.
So, hardcoding values is not an option.


CASE and DECODE would require hardcoding; that you
know exactly what the values will be. If new rows with
new and different values were entered into the field,
you would have to re hardcode the query.



I had looked at PSOUG actually.
http://www.psoug.org/reference/model_clause.html


Where at least some of the examples were the same as from:
http://download.oracle.com/docs/cd/B...3/sqlmodel.htm


Both examples required hardcoding the value:


RULES (sales['Bounce', 2005] = 100 + MAX(sales)['Bounce',
year BETWEEN 1998 AND 2002])

ie. 'BOUNCE', refers to product = 'BOUNCE'



With 10G's new features, is it possible to do this kind of thing,
dynamically getting a different number of columns back?
In particular with the MODEL clause?

Perhaps a new feature in Oracle 11?

What is the secret please?

dba_222@yahoo.com wrote:
> OK, I'm back. No emergencies today.
>
> Our data changes on a daily, weekly and monthly basis.
> So, hardcoding values is not an option.

Then use variables. They've been around a very long time and seem
to work reasonably well. <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Aug 13, 10:22 pm, dba_...@yahoo.com wrote:
> OK, I'm back. No emergencies today.
>
> Our data changes on a daily, weekly and monthly basis.
> So, hardcoding values is not an option.
>
> CASE and DECODE would require hardcoding; that you
> know exactly what the values will be. If new rows with
> new and different values were entered into the field,
> you would have to re hardcode the query.
>
> I had looked at PSOUG actually.http://www.psoug.org/reference/model_clause.html
>
> Where at least some of the examples were the same as from:http://download.oracle.com/docs/cd/B.../b14223/sqlmod...
>
> Both examples required hardcoding the value:
>
> RULES (sales['Bounce', 2005] = 100 + MAX(sales)['Bounce',
> year BETWEEN 1998 AND 2002])
>
> ie. 'BOUNCE', refers to product = 'BOUNCE'
>
> With 10G's new features, is it possible to do this kind of thing,
> dynamically getting a different number of columns back?
> In particular with the MODEL clause?
>
> Perhaps a new feature in Oracle 11?
>
> What is the secret please?

No, 11g's PIVOT clause still requires you to specify literal values to
define the columns. AFAIK there is no way for a static query to return
a varying number of columns. Possibly there is a way to do this by
unleashing the dark power of XQuery, but let's not go there.