Received: from odysseus2.dohle.com (root@odysseus2.dohle.com [194.127.185.20]) by hub.org (8.10.1/8.10.1) with ESMTP id e747PYG24432 for ; Fri, 4 Aug 2000 03:25:34 -0400 (EDT) Received: from majestix.dohle.com (majestix.dohle.com [194.127.190.1]) by odysseus2.dohle.com (8.9.3/8.9.3) with SMTP id JAA24319; Fri, 4 Aug 2000 09:25:23 +0200 Received: from wpaul.dohle.com by majestix.dohle.com id aa14012; 4 Aug 100 9:25 CETDST Message-ID: <398A7112.E46D3E11@dohle.com> Date: Fri, 04 Aug 2000 09:30:26 +0200 From: Volker Paul Organization: Dohle Handelsgruppe Systemberatung GmbH X-Mailer: Mozilla 4.05 [en] (WinNT; I) MIME-Version: 1.0 To: Dana.Reed@clinicaldatacare.com CC: pgsql-sql@postgresql.org Subject: Re: SQL (table transposition) References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200008/35 Dana.Reed@clinicaldatacare.com wrote: > > Is there any way to get table T3 (below) from T1 and T2 using SQL (select, view, etc)? > > T3 is basically all INDEX values from T1 matched to IND from T2 with the corresponding KEY/VALUE pairs transposed from rows to columns. > > ------- > |INDEX| (T1) > ------- > | 1 | > | 2 | > | 3 | > ------- > > ----------------- > |IND|KEY| VALUE | (T2) > ----------------- > | 1 | 1 | val_a | > | 1 | 2 | val_b | > | 1 | 3 | val_c | > | 2 | 1 | val_d | > | 2 | 2 | val_e | > | 3 | 1 | val_f | > | 3 | 3 | val_g | > ----------------- > > ---------------------------------- > |T1_INDEX|KEY1VAL|KEY2VAL|KEY3VAL| (T3) > ---------------------------------- > | 1 | val_a | val_b | val_c | > | 2 | val_d | val_e | | > | 3 | val_f | | val_g | > ---------------------------------- > I think what you are looking for is cross tabulation, TRANSFORM statement, but I don't know if that is supported by PostgreSQL. Volker Paul