On Sat, 2009-01-31 at 18:32 +0000, Greg Stark wrote: > On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez > <alvarezp@xxxxxxxxxxxxxxxx> wrote: > > > > It doesn't really matter. Since crosstabs are just a presentational > > variation to a query with aggregate functions and GROUP BY clauses, > > > Why are crosstabs just a presentation issue any more than GROUP BY or ORDER BY? If I understood your question correctly, it is because you can take any crosstab and convert it to a vertical list of values that generate it, with a maximum number of columns, and easily WHERE-able. For example, a accumulative percent-based grade report: +-------------+----+----+----+----+---------+ | Assignment | P1 | P2 | P3 | P4 | Average | +-------------+----+----+----+----+---------+ | Assignment1 | 95 | 90 | 99 | | 94.67 | | Assignment2 | 90 | 90 | 91 | | 90.33 | | Assignment3 | 85 | 80 | 95 | | 86.67 | +-------------+----+----+----+----+---------+ The data source is: +-------------+--------+-------+ | Assignment | Period | Value | +-------------+--------+-------+ | Assignment1 | P1 | 95 | | Assignment1 | P2 | 90 | | Assignment1 | P3 | 99 | | Assignment2 | P1 | 90 | | Assignment2 | P2 | 90 | | Assignment2 | P3 | 91 | | Assignment3 | P1 | 85 | | Assignment3 | P2 | 80 | | Assignment3 | P3 | 95 | +-------------+--------+-------+ ... even if P4 doesn't exist yet. You can have a crosstab with a dynamic number of columns where P4 doesn't appear as a column, or a pre-defined number of columns. And even if each value from the crosstab is an aggregate function like count() or max() the data source first is prepared as a select/where and maybe a group by and then transformed into a crosstab. In any case, the results are the same as GROUPing BY from the data source. +-------------+---------+ | Assignment | Average | +-------------+---------+ | Assignment1 | 94.67 | | Assignment2 | 90.33 | | Assignment3 | 86.67 | +-------------+---------+ A crosstab is not but a presentational transform of the data set. Any information you would eventually need can be taken from the original data source, one way or another. That's why dynamic-column crosstab are not a problem, and the DBMS should not worry about providing the information about the columns, maybe by simply not allowing the dynamic-column ones in subqueries. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
