Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Syntax for simple (?) pivot with MODEL clause

Syntax for simple (?) pivot with MODEL clause

From: <dba_222_at_yahoo.com>
Date: Thu, 02 Aug 2007 09:01:43 -0700
Message-ID: <1186070503.168269.77770@i13g2000prf.googlegroups.com>


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! Received on Thu Aug 02 2007 - 11:01:43 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US