pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: secret <secret@kearneydev.com>
To: The Hermit Hacker <scrappy@hub.org>
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Odd "problem", not sure if there is a solution ....
Date: Fri, 26 Mar 1999 15:08:13 -0500
Message-ID: <36FBE92D.B5E2BB93@kearneydev.com> (raw)
References: <Pine.BSF.4.05.9903252343280.389-100000@thelab.hub.org>

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' <cough>):

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.






view thread (6+ messages)  latest in thread

Message-ID: <36FBE92D.B5E2BB93@kearneydev.com>
Permalink:  ../36FBE92D.B5E2BB93@kearneydev.com/
Also on:    postgresql.org/message-id/36FBE92D.B5E2BB93@kearneydev.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: secret@kearneydev.com, scrappy@hub.org
  Subject: Re: [SQL] Odd "problem", not sure if there is a solution ....
  In-Reply-To: <36FBE92D.B5E2BB93@kearneydev.com>
  References: <Pine.BSF.4.05.9903252343280.389-100000@thelab.hub.org> <36FBE92D.B5E2BB93@kearneydev.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox