Re: Pet Peeves?

Gregory Stark wrote:

"Daniel Verite" <daniel@manitou-mail.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. :)