pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOdd "problem", not sure if there is a solution ....
6+ messages / 5 participants
[nested] [flat]
* Odd "problem", not sure if there is a solution ....
@ 1999-03-26 03:46 The Hermit Hacker <scrappy@hub.org>
1999-03-26 20:08 ` Re: [SQL] Odd "problem", not sure if there is a solution .... secret <secret@kearneydev.com>
1999-03-28 15:48 ` Re: [SQL] Odd "problem", not sure if there is a solution .... Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 2 replies; 6+ messages in thread
From: The Hermit Hacker @ 1999-03-26 03:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Odd "problem", not sure if there is a solution ....
1999-03-26 03:46 Odd "problem", not sure if there is a solution .... The Hermit Hacker <scrappy@hub.org>
@ 1999-03-26 20:08 ` secret <secret@kearneydev.com>
1999-03-31 13:11 ` Re: [SQL] Odd "problem", not sure if there is a solution .... Chairudin Sentosa <chairudin@prima.net.id>
1 sibling, 1 reply; 6+ messages in thread
From: secret @ 1999-03-26 20:08 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Odd "problem", not sure if there is a solution ....
1999-03-26 03:46 Odd "problem", not sure if there is a solution .... The Hermit Hacker <scrappy@hub.org>
1999-03-26 20:08 ` Re: [SQL] Odd "problem", not sure if there is a solution .... secret <secret@kearneydev.com>
@ 1999-03-31 13:11 ` Chairudin Sentosa <chairudin@prima.net.id>
0 siblings, 0 replies; 6+ messages in thread
From: Chairudin Sentosa @ 1999-03-31 13:11 UTC (permalink / raw)
To: secret <secret@kearneydev.com>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
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' <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.
Hi David,
I like your solution.
However, is there a better way to remove the constraint?
Thanks
Regards,
Chai
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [SQL] Odd "problem", not sure if there is a solution ....
1999-03-26 03:46 Odd "problem", not sure if there is a solution .... The Hermit Hacker <scrappy@hub.org>
@ 1999-03-28 15:48 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 6+ messages in thread
From: Herouth Maoz @ 1999-03-28 15:48 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
At 05:46 +0200 on 26/03/1999, 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?
The question is whether you expect your query to return rows in the above
format, where each of the answers is a separate field, or you could settle
for all the answers returned in one text field.
SQL does not provide for queries that return a different number of fields
for each row. What I'd do in such a case is to create an aggregate function
based on text concatenation ('||'), and either use the result as is or
split it on the client side through known delimiters.
Another approach is to query this table separately and do the grouping on
the client side.
I've used the first approach in one of my projects, if you are interested
in the solution.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [SQL] Odd "problem", not sure if there is a solution ....
@ 1999-03-26 16:29 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jackson, DeJuan @ 1999-03-26 16:29 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
It's possible, but not on the SQL end. You'll have to do the work on the
display end. If you're using PHP or Perl I'd be happy to give you a hand.
-DEJ
> -----Original Message-----
> From: The Hermit Hacker [mailto:scrappy@hub.org]
> Sent: Thursday, March 25, 1999 9:47 PM
> To: pgsql-sql@postgreSQL.org
> Subject: [SQL] Odd "problem", not sure if there is a solution ....
>
>
>
> 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
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: [SQL] Odd "problem", not sure if there is a solution ....
@ 1999-03-31 16:54 Jackson, DeJuan <djackson@cpsgroup.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jackson, DeJuan @ 1999-03-31 16:54 UTC (permalink / raw)
To: chairudin@prima.net.id, secret <secret@kearneydev.com>; +Cc: The Hermit Hacker <scrappy@hub.org>; pgsql-sql
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' <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.
>
> Hi David,
>
> I like your solution.
> However, is there a better way to remove the constraint?
>
> Thanks
>
> Regards,
> Chai
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~1999-03-31 16:54 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-26 03:46 Odd "problem", not sure if there is a solution .... The Hermit Hacker <scrappy@hub.org>
1999-03-26 20:08 ` secret <secret@kearneydev.com>
1999-03-31 13:11 ` Chairudin Sentosa <chairudin@prima.net.id>
1999-03-28 15:48 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-26 16:29 RE: [SQL] Odd "problem", not sure if there is a solution .... Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-31 16:54 RE: [SQL] Odd "problem", not sure if there is a solution .... Jackson, DeJuan <djackson@cpsgroup.com>
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