Thread: Crossed Reference Query
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
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
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 >