X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 91BEAD1BAC8 for ; Sun, 22 Feb 2004 18:55:00 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 31711-09 for ; Sun, 22 Feb 2004 14:54:53 -0400 (AST) Received: from localhost.localdomain (adsl-66-143-226-95.dsl.snantx.swbell.net [66.143.226.95]) by svr1.postgresql.org (Postfix) with ESMTP id 942BED1BAA5 for ; Sun, 22 Feb 2004 14:54:51 -0400 (AST) Received: from localhost.localdomain (IDENT:ebQOyybvZqUa27krNpabp/SF2JIAwtDz@localhost.localdomain [127.0.0.1]) by localhost.localdomain (8.12.5/8.12.5) with ESMTP id i1MIsxw5012090 (version=TLSv1/SSLv3 cipher=EDH-RSA-DES-CBC3-SHA bits=168 verify=NO) for ; Sun, 22 Feb 2004 12:55:00 -0600 Received: (from joe@localhost) by localhost.localdomain (8.12.5/8.12.5/Submit) id i1MIsxra012088 for pgsql-novice@postgresql.org; Sun, 22 Feb 2004 12:54:59 -0600 Date: Sun, 22 Feb 2004 12:54:59 -0600 From: joseph speigle To: pgsql-novice@postgresql.org Subject: Re: [SQL] crosstabs Message-ID: <20040222185459.GA12077@www.sirfsup.com> Mail-Followup-To: joseph speigle , pgsql-novice@postgresql.org References: <6C0CF58A187DA5479245E0830AF84F420AFC0E@poweredge.attiksystem.ch> <94113164570.20040219182353@ugyvitelszolgaltato.hu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <94113164570.20040219182353@ugyvitelszolgaltato.hu> User-Agent: Mutt/1.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/178 X-Sequence-Number: 9649 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