"Daniel Verite" <daniel@xxxxxxxxxxxxxxxx> 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. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
