Re: Pet Peeves?

On Sat, 2009-01-31 at 18:32 +0000, Greg Stark wrote:
> On Sat, Jan 31, 2009 at 5:34 PM, Octavio Alvarez
> <alvarezp(at)alvarezp(dot)ods(dot)org> 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.