Re: [SQL] crosstabs - Mailing list pgsql-novice

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=(SELECTcode 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=(SELECTcode 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=(SELECTcode 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