Received: from ns1.prima.net.id ([202.57.0.8]) by hub.org (8.9.2/8.9.1) with ESMTP id BAA46568 for ; Wed, 31 Mar 1999 01:11:57 -0500 (EST) (envelope-from chairudin@prima.net.id) Received: from prima.net.id (mail.prima.net.id [202.57.0.8]) by ns1.prima.net.id (8.8.4/8.7.2) with ESMTP id UAA18007; Wed, 31 Mar 1999 20:11:30 +0700 (GMT) Sender: root@ns1.prima.net.id Message-ID: <37021F02.72D1FE8@prima.net.id> Date: Wed, 31 Mar 1999 06:11:30 -0700 From: Chairudin Sentosa Reply-To: chairudin@prima.net.id Organization: PT. Khasanah Timur Indonesia X-Mailer: Mozilla 4.5 [en] (X11; I; SunOS 5.5.1 sun4u) X-Accept-Language: en MIME-Version: 1.0 To: secret CC: The Hermit Hacker , pgsql-sql@postgreSQL.org Subject: Re: [SQL] Odd "problem", not sure if there is a solution .... References: <36FBE92D.B5E2BB93@kearneydev.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit secret wrote: > 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. Hi David, I like your solution. However, is there a better way to remove the constraint? Thanks Regards, Chai