From: | robert kraus <rob_kra(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | pivoting, crosstabbing and almost there ! |
Date: | 2002-07-25 18:45:18 |
Message-ID: | 20020725184518.36180.qmail@web14303.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
I am trying to get a pivoted result from a query. The
pivoting works, however I want to eliminate
some of the rows, which have no value at all in every
column but the name column.
Current result:
name first second third
bill
bob 90
sue 85 90 95
desired:
name first second third
bob 90
sue 85 90 95
Of course this example is extremly oversimplified. I
will not know the actual exams values, but retrieve
them
dynamically. I know that there is a patch now for
doing crosstabs (thanks!), but a more general SQL
solution would
be better ( maybe it will have to run on other dbs ).
Thank you very much for your answers.
robert
Example code:
CREATE TABLE students ( name varchar(10), NOT NULL
UNIQUE, PRIMARY KEY( name ) );
CREATE TABLE exams( exam varchar(10) NOT NULL UNIQUE,
PRIMARY KEY( exam ) );
CREATE TABLE scores( name varchar(10), exam
varchar(10), score int,
FOREIGN KEY (name) REFERENCES students, FOREIGN KEY(
exam ) REFERENCES exams );
INSERT INTO students VALUES ( 'Bill' );
INSERT INTO students VALUES ('Bob');
INSERT INTO students VALUES ('Sue');
INSERT INTO exams VALUES( 'first' );
INSERT INTO exams VALUES( 'second' );
INSERT INTO exams VALUES('third');
INSERT INTO scores VALUES( 'Bill', 'first', 50 );
INSERT INTO scores VALUES( 'Bill', 'second', 60 );
INSERT INTO scores VALUES( 'Bill', 'third', 55 );
INSERT INTO scores VALUES( 'Bob', 'first', 70 );
INSERT INTO scores VALUES( 'Bob', 'second', 90 );
INSERT INTO scores VALUES( 'Bob', 'third', 85 );
INSERT INTO scores VALUES( 'Sue', 'first', 85 );
INSERT INTO scores VALUES( 'Sue', 'second', 90 );
INSERT INTO scores VALUES( 'Sue', 'third', 95 );
SELECT students.name,
( SELECT score FROM scores
WHERE (
students.name = scores.name
AND
scores.exam = 'first'
AND
scores.score > '70'
)
) AS first,
( SELECT score FROM scores
WHERE (
students.name = scores.name
AND
scores.exam = 'second'
AND
scores.score > '80'
)
) AS second,
( SELECT score FROM scores
WHERE (
students.name = scores.name
AND
scores.exam = 'third'
AND
scores.score > '90'
)
) AS third
FROM students;
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Stephan Szabo | 2002-07-25 19:22:52 | Re: pivoting, crosstabbing and almost there ! |
Previous Message | Andrew Sullivan | 2002-07-25 18:36:40 | Re: Error while dropping a table |