X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 2D0D1D1E140 for ; Thu, 19 Feb 2004 16:02:19 +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 14357-01 for ; Thu, 19 Feb 2004 12:02:14 -0400 (AST) Received: from poweredge.attiksystem.ch (adsl-soholight-4-c59-p029.vtx.ch [212.147.59.29]) by svr1.postgresql.org (Postfix) with ESMTP id 69BB3D1E13C for ; Thu, 19 Feb 2004 12:02:12 -0400 (AST) content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Subject: crosstabs Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 Date: Thu, 19 Feb 2004 17:02:10 +0100 Message-ID: <6C0CF58A187DA5479245E0830AF84F420AFC0E@poweredge.attiksystem.ch> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: crosstabs Thread-Index: AcP3AeBNEvUNrHGAReCRxYuzS+t4lg== From: "Philippe Lang" To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/274 X-Sequence-Number: 16964 Hello, I need to do something similar to a cross tabulation, but without any aggregation. Here is an example: ------------------------------------------------------------ -- TABLE DEFINITION ------------------------------------------------------------=20 CREATE TABLE public.type ( code char(1) PRIMARY KEY, type varchar(30) ) WITHOUT OIDS;=20 =20 CREATE TABLE public.master ( id int4 PRIMARY KEY, master_name varchar(30) ) WITHOUT OIDS; CREATE TABLE public.detail ( id int4 PRIMARY KEY, code_type char(1) REFERENCES public.type, id_master int4 REFERENCES public.master, detail_name varchar(30) ) WITHOUT OIDS;=20 =20 ------------------------------------------------------------ -- DATA ------------------------------------------------------------=20 INSERT INTO public.type VALUES('A', 'TA');=20 INSERT INTO public.type VALUES('B', 'TB');=20 INSERT INTO public.type VALUES('C', 'TC');=20 =20 INSERT INTO public.master VALUES(1, 'M1');=20 INSERT INTO public.master VALUES(2, 'M2');=20 INSERT INTO public.master VALUES(3, 'M3');=20 =20 INSERT INTO public.detail VALUES(1, 'A', 1, 'M1, D1');=20 INSERT INTO public.detail VALUES(2, 'B', 1, 'M1, D2');=20 INSERT INTO public.detail VALUES(3, 'A', 1, 'M1, D3');=20 INSERT INTO public.detail VALUES(4, 'C', 1, 'M1, D4');=20 INSERT INTO public.detail VALUES(5, 'C', 2, 'M2, D1');=20 INSERT INTO public.detail VALUES(6, 'A', 3, 'M3, D1');=20 INSERT INTO public.detail VALUES(7, 'A', 3, 'M3, D2');=20 INSERT INTO public.detail VALUES(8, 'B', 3, 'M3, D3');=20 INSERT INTO public.detail VALUES(9, 'A', 3, 'M3, D4');=20 INSERT INTO public.detail VALUES(10, 'B', 3, 'M3, D5');=20 INSERT INTO public.detail VALUES(11, 'C', 3, 'M3, D6');=20 INSERT INTO public.detail VALUES(12, 'C', 3, 'M3, D7');=20 =20 ------------------------------------------------------------ -- QUERY ------------------------------------------------------------=20 SELECT=20 master_name,=20 detail_name,=20 type=20 =20 FROM=20 master INNER JOIN detail=20 ON master.id =3D detail.id_master=20 =20 INNER JOIN type=20 ON detail.code_type =3D type.code=20 =20 ORDER by master.id, detail.id;=20 ------------------------------------------------------------=20 The result of that is: ---------------------------------- master_name | detail_name | type | ---------------------------------- M1 | M1, D1 | TA | M1 | M1, D2 | TB | M1 | M1, D3 | TA | M1 | M1, D4 | TC | M2 | M2, D1 | TC | M3 | M3, D1 | TA | M3 | M3, D2 | TA | M3 | M3, D3 | TB | M3 | M3, D4 | TA | M3 | M3, D5 | TB | M3 | M3, D6 | TC | M3 | M3, D7 | TC | ---------------------------------- I need something like this: ---------------------------------------- master_name | TA | TB | TC | ---------------------------------------- M1 | M1, D1 | | | M1 | | M1, D2 | | M1 | M1, D3 | | | M1 | | | M1, D4 | M2 | | | M2, D1 | M3 | M3, D1 | | | M3 | M3, D2 | | | M3 | | M3, D3 | | M3 | M3, D4 | | | M3 | | M3, D5 | | M3 | | | M3, D6 | M3 | | | M3, D7 | ---------------------------------------- Does anyone know how to do that in Postgresql? I run version 7.3.4. Thanks for any idea you might have. Philippe Lang