Received: from nts1.rtp.epa.gov (nts1.rtp.epa.gov [134.67.208.208]) by hub.org (8.10.1/8.10.1) with ESMTP id e73FkgG75142 for ; Thu, 3 Aug 2000 11:46:42 -0400 (EDT) Received: from epamail.epa.gov (IDENT:mvs@wolfpack.rtpnc.epa.gov [134.67.139.59]) by nts1.rtp.epa.gov (8.9.3/8.9.3) with ESMTP id LAA31330; Thu, 3 Aug 2000 11:50:21 -0400 (EDT) Message-ID: <398993D3.D421AAB7@epamail.epa.gov> Date: Thu, 03 Aug 2000 11:46:27 -0400 From: Mark Volpe X-Mailer: Mozilla 4.74 [en] (X11; U; Linux 2.2.14-15mdk i586) X-Accept-Language: en 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=iso-8859-1 Content-Transfer-Encoding: 8bit X-Archive-Number: 200008/28 Hope you like black magic :) SELECT IND AS T1_INDEX, MIN(CASE WHEN KEY=1 THEN VALUE ELSE NULL END) AS KEY1VAL, MIN(CASE WHEN KEY=2 THEN VALUE ELSE NULL END) AS KEY2VAL, MIN(CASE WHEN KEY=3 THEN VALUE ELSE NULL END) AS KEY3VAL FROM T2 GROUP BY IND ORDER BY IND; Mark 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 | > ---------------------------------- > > Thanks for any suggestions > > med vänlig hälsning > /Dana