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

On Aug 13, 10:22 pm, dba_..._at_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/B19306_01/server.102/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. Received on Thu Aug 16 2007 - 16:51:20 CEST