Re: [SQL] crosstabs

PL> ------------------------------------------------------------
PL> -- QUERY
PL> ------------------------------------------------------------
PL> SELECT
PL> master_name,
PL> detail_name,
PL> type

PL> FROM
PL> master INNER JOIN detail
PL> ON master.id = detail.id_master

PL> INNER JOIN type
PL> ON detail.code_type = type.code

PL> ORDER by master.id, detail.id;

PL> ------------------------------------------------------------

PL> The result of that is:

PL> ----------------------------------
PL> master_name | detail_name | type |
PL> ----------------------------------
PL> M1 | M1, D1 | TA |
PL> M1 | M1, D2 | TB |
PL> M1 | M1, D3 | TA |
PL> M1 | M1, D4 | TC |
PL> M2 | M2, D1 | TC |
PL> M3 | M3, D1 | TA |
PL> M3 | M3, D2 | TA |
PL> M3 | M3, D3 | TB |
PL> M3 | M3, D4 | TA |
PL> M3 | M3, D5 | TB |
PL> M3 | M3, D6 | TC |
PL> M3 | M3, D7 | TC |
PL> ----------------------------------

PL> I need something like this:

PL> ----------------------------------------
PL> master_name | TA | TB | TC |
PL> ----------------------------------------
PL> M1 | M1, D1 | | |
PL> M1 | | M1, D2 | |
PL> M1 | M1, D3 | | |
PL> M1 | | | M1, D4 |
PL> M2 | | | M2, D1 |
PL> M3 | M3, D1 | | |
PL> M3 | M3, D2 | | |
PL> M3 | | M3, D3 | |
PL> M3 | M3, D4 | | |
PL> M3 | | M3, D5 | |
PL> M3 | | | M3, D6 |
PL> M3 | | | M3, D7 |
PL> ----------------------------------------

PL> Does anyone know how to do that in Postgresql? I run version 7.3.4.

PL> Thanks for any idea you might have.

PL> Philippe Lang

Maybe you can use the CASE construct.

select mastername, case when type='TA' then detail_name else '' end as ta, case .... as tb, case ... as ts from ...

I don't try this, but maybe...

DAQ