Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.2/8.9.1) with ESMTP id LAA11983 for ; Wed, 31 Mar 1999 11:54:19 -0500 (EST) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id KAA26506; Wed, 31 Mar 1999 10:54:12 -0600 (CST) Received: from unknown(144.210.12.10) by gauntlet.bjke.com via smap (4.1) id xma026086; Wed, 31 Mar 99 10:53:33 -0600 Received: by cpsmail with Internet Mail Service (5.5.2448.0) id ; Wed, 31 Mar 1999 10:54:02 -0600 Message-ID: From: "Jackson, DeJuan" To: chairudin@prima.net.id, secret Cc: The Hermit Hacker , pgsql-sql@postgreSQL.org Subject: RE: [SQL] Odd "problem", not sure if there is a solution .... Date: Wed, 31 Mar 1999 10:54:01 -0600 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain; charset="iso-8859-1" Look at dynamic length arrays. -DEJ > -----Original Message----- > From: Chairudin Sentosa [mailto:chairudin@prima.net.id] > Sent: Wednesday, March 31, 1999 7:12 AM > To: secret > Cc: The Hermit Hacker; pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Odd "problem", not sure if there is a solution .... > > > 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 > >