Re: [SQL] crosstabs

On Thu, Feb 19, 2004 at 06:23:53PM +0100, daq wrote:
>
>
> 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
the part which actually does it is the last select statement.

drop table master cascade;
create table master (
id int4 UNIQUE,
master_name varchar(10)
);

drop table type cascade;
create table type (
code serial unique,
type varchar(10)
);

drop table detail cascade;
create table detail (
id serial unique,
master_id int4 REFERENCES master(id),
detail_name varchar(10),
type_code int4 REFERENCES type(code)
);

insert into master (id, master_name) values ('1','M1');
insert into master (id, master_name) values ('2','M2');
insert into master (id, master_name) values ('3','M3');

insert into type (code,type) values (1,'TA');
insert into type (code,type) values (2,'TB');
insert into type (code,type) values (3,'TC');

insert into detail (master_id, detail_name, type_code) values ('1','M1,D1',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D1',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D3',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D4',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D5',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D6',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D8',1);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D7',1);
insert into detail (master_id, detail_name, type_code) values ('1','M1,D2',2);
insert into detail (master_id, detail_name, type_code) values (3,'M3,D2',2);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);
insert into detail (master_id, type_code) values (2,3);
insert into detail (master_id, type_code) values (1,3);
insert into detail (master_id, detail_name, type_code) values (2,'M2,D4',3);

drop view TA;
drop view TB;
drop view TC;

CREATE VIEW TA AS SELECT detail.id AS detail_id, detail_name AS TA, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TA') AND detail.master_id=master.id;
SELECT * FROM TA;
CREATE VIEW TB AS SELECT detail.id AS detail_id, detail_name AS TB, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TB') AND detail.master_id=master.id;
SELECT * FROM TB;
CREATE VIEW TC AS SELECT detail.id AS detail_id, detail_name AS TC, master.id AS master_id FROM master,detail WHERE detail.type_code=(SELECT code FROM type WHERE type.type = 'TC') AND detail.master_id=master.id;
SELECT * FROM TA;
SELECT * FROM TB;
SELECT * FROM TC;

SELECT master.master_name, TA.TA, TB.TB, TC.TC
FROM TA
FULL OUTER JOIN TB
ON ta.detail_id = tb.detail_id
FULL OUTER JOIN TC
ON ta.detail_id = tc.detail_id
JOIN master ON master.id = ta.master_id OR master.id = tb.master_id OR master.id = tc.master_id;

which gives:

master_name | ta | tb | tc
-------------+-------+-------+-------
M1 | M1,D1 | |
M1 | M1,D3 | |
M3 | M3,D1 | |
M3 | M3,D3 | |
M3 | M3,D4 | |
M3 | M3,D5 | |
M3 | M3,D6 | |
M3 | M3,D8 | |
M3 | M3,D7 | |
M1 | | M1,D2 |
M3 | | M3,D2 |
M2 | | | M2,D4
M2 | | |
M1 | | |
M2 | | | M2,D4
(15 rows)

joe
--
speigle
www.sirfsup.com