Thread: pivoting, crosstabbing and almost there !

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

> 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.

Maybe something like:

SELECT * from (
 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
) AS c
where c.first is not null or c.second is not null or
 c.third is not null;