Received: from nexus.kearneydev.com (IDENT:root@nexus.kearneydev.com [208.136.214.2]) by hub.org (8.9.2/8.9.1) with ESMTP id PAA75300 for ; Fri, 26 Mar 1999 15:08:16 -0500 (EST) (envelope-from secret@kearneydev.com) Received: from kearneydev.com (atu.kearneydev.com [208.136.214.93]) by nexus.kearneydev.com (8.8.5/8.8.5) with ESMTP id PAA22440; Fri, 26 Mar 1999 15:08:10 -0500 Message-ID: <36FBE92D.B5E2BB93@kearneydev.com> Date: Fri, 26 Mar 1999 15:08:13 -0500 From: secret Organization: Kearney Development Co., Inc. X-Mailer: Mozilla 4.5 [en] (WinNT; U) X-Accept-Language: en MIME-Version: 1.0 To: The Hermit Hacker , pgsql-sql@postgresql.org Subject: Re: [SQL] Odd "problem", not sure if there is a solution .... References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit The Hermit Hacker wrote: > am working up a database for client that contains answers for online > testing...right now, the 'tables' look like: > > userid Q1 Q2 Q3 Q4 Q5 Q6 Q7 etc > > I want to change it so that its more generic, as: > > userid question_no answer > > so that instead of one row per user, there are X rows... > > the problem is that, somehow, I want the client to be able to view the > results as the first format (ie. one line per user) with it being stored > as multiple lines per user... > > Possible? > > Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy > Systems Administrator @ hub.org > primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org Sure it's possible, if you can live with the constrant that every person must answer every question... (Else that person will kind of 'disappear' ): CREATE TABLE q ( person int4, -- Person ID# q int4, -- Question # a int4 ) -- Answer ftc=> select * from q where q in (1,2); person|q| a ------+-+-- 1|1| 1 1|2| 1 2|1|21 2|2|22 (4 rows) SELECT q1.person, q1.a, q2.a FROM q as q1, q as q2 WHERE q1.person=q2.person AND q1.q = 1 AND q2.q = 2 ; person| a| a ------+--+-- 1| 1| 1 2|21|22 (2 rows) Your probably want to rename "a" to a1 and a2, but you can expand this to N questions... The performance will suck if your table is pretty big, so I'd arrange to move things to a history file every so often... Keep in mind people will disappear if they are missing the answer to any question. David Secret MIS Director Kearney Development Co., Inc.