Re: Crossed Reference Query

Eduardo Mylonas - Externo wrote:
>
> MS Access has a functionality which is a type of query called "Crossed
> Reference Query" (At least, that's the lame translation i got to come up
> with).
>

Take a look at crosstab() in contrib/tablefunc. The one in the 7.3.x
distribution has a limitation in that your row source query must ensure
there is a row for each row identifier for each category, even if it is
NULL. E.g. if your source query must produce:

row1 cat1 val11
row1 cat2 val12
row1 cat3 val13
row2 cat1 val21
row2 cat2 NULL
row2 cat3 val23

and *not* this:

row1 cat1 val11
row1 cat2 val12
row1 cat3 val13
row2 cat1 val21
row2 cat3 val23

There is an improved option in 7.4 (and available for 7.3.x here:
http://www.joeconway.com/ ) that allows the latter example to work
correctly. See crosstab(text, text) in the README for more detail.

HTH,

Joe