Thread: Crossed Reference Query

Crossed Reference Query

From
"Eduardo Mylonas - Externo"
Date:

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).

The syntax is something like this:
TRANSFORM Max(Consulta1.nome) AS [O valor]
SELECT Consulta1.usuario_id, Max(Consulta1.nome) AS [Total de nome]
FROM Consulta1
GROUP BY Consulta1.usuario_id
PIVOT Consulta1.textoitem;

The query Consulta1 is:
SELECT sig_permissoes.usuario_id, sig_menudecontrole.textoitem,
sig_niveis.nome
FROM sig_niveis INNER JOIN (sig_menudecontrole INNER JOIN sig_permissoes ON
sig_menudecontrole.menu_id = sig_permissoes.menu_id) ON sig_niveis.nivel_id
= sig_permissoes.nivel_id
ORDER BY sig_permissoes.usuario_id, sig_menudecontrole.painel_id,
sig_menudecontrole.numeroitem;

Well, i want to know whether is possible to do something like this over
PostgreSQL. If it's needed perl scripts or some other type of scripting, for
me that's ok. the thing is that i need to have that.

Is it possible?

Thanks to all.

Eduardo Mylonas da Silva
Automat Eng. de Automação
Desenvolovimento
mailto: emylonas@automat.com.br
www.automat.com.br


Re: Crossed Reference Query

From
Joe Conway
Date:
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


Re: Crossed Reference Query

From
Dennis Gearon
Date:
ADODB library has a pivot table generating funciton.

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).
>
> The syntax is something like this:
> TRANSFORM Max(Consulta1.nome) AS [O valor]
> SELECT Consulta1.usuario_id, Max(Consulta1.nome) AS [Total de nome]
> FROM Consulta1
> GROUP BY Consulta1.usuario_id
> PIVOT Consulta1.textoitem;
>
> The query Consulta1 is:
> SELECT sig_permissoes.usuario_id, sig_menudecontrole.textoitem,
> sig_niveis.nome
> FROM sig_niveis INNER JOIN (sig_menudecontrole INNER JOIN sig_permissoes ON
> sig_menudecontrole.menu_id = sig_permissoes.menu_id) ON sig_niveis.nivel_id
> = sig_permissoes.nivel_id
> ORDER BY sig_permissoes.usuario_id, sig_menudecontrole.painel_id,
> sig_menudecontrole.numeroitem;
>
> Well, i want to know whether is possible to do something like this over
> PostgreSQL. If it's needed perl scripts or some other type of scripting, for
> me that's ok. the thing is that i need to have that.
>
> Is it possible?
>
> Thanks to all.
>
> Eduardo Mylonas da Silva
> Automat Eng. de Automação
> Desenvolovimento
> mailto: emylonas@automat.com.br
> www.automat.com.br
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>