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