Gregory Stark wrote:
> "Daniel Verite" <daniel(at)manitou-mail(dot)org> writes:
>
>
>> Gregory Stark wrote:
>>
>>
>>> Is it the hierarchical query ability you're looking for or pivot?
>>> The former we are actually getting in 8.4.
>>>
>>> AFAIK even in systems with pivot you still have to
>>> declare a fixed list of columns in advance anyways.
>>> Do you see a system where it works differently?
>>>
>> MS-Access SQL has a TRANSFORM clause that allows for crosstab queries without
>> the need to know in advance the number of columns:
>> http://msdn.microsoft.com/en-us/library/bb208956.aspx
>>
>
> That's puzzling. I wonder what they do about clients requesting info about the
> results. Or for that matter such queries being used in subqueries or anywhere
> else where the surrounding code needs to know the type of results to expect.
>
>
>> As for Oracle, it wasn't possible until recently but now 11g has the PIVOT
>> clause:
>> http://www.oracle.com/technology/pub/articles/oracle-database-11g-top-f
>> eatures/11g-pivot.html
>>
>
> From this the result columns do need to be explicitly listed in advance unless
> you're asking for the pivot to be into an xml blob which seems like a whole
> different feature really.
>
>
>> In contrast of these clauses, PG's contrib/tablefunc looks more limited and
>> quite harder to use.
>>
>
> Incidentally, the work-around I've used in the past was to aggregate the rows
> into an array instead of separate columns. Definitely not the same of course,
> just a work-around.
>
> I think PIVOT is enticing too. It'll be interesting to see what happens in the
> standard with the divergence between MSSQL and Oracle.
>
>
PIVOT would prove very valuable to my application. :)