Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Syntax for simple (?) pivot with MODEL clause
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 NULLNUMBER(10,2) Select channel_id, count(*)
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(*)
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! Received on Thu Aug 02 2007 - 11:01:43 CDT
![]() |
![]() |