SQL Transpose Table

[Gregory S Moy]

I'm playing around with the following tables and need some advice.

TablePeople (5000 records)
peopleID
sex
age
race

TableExam (5000 records)
examID
date
peopleID
TableQuestions (5000 records)
questionID
questionFieldname (QFN0001,QFN0002,QFN0003, ... ,QFN5000)
TableAnswers (25,000,000 records)
examID
questionID
answer

SQL Query Statement Needed!
Query the database to return fields from TablePeople and TableAnswers
(from 1 to 255 questionFieldnames)
Example Query and return data from:
ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011
I know how to join the tables and return the following recordset
ExamID, sex, age, race, QuestionFieldName, AnswerID
100001,M,40,White,QFN0001,0
100001,M,40,White,QFN0002,5
100001,M,40,White,QFN0003,6
100001,M,40,White,QFN0005,3
100001,M,40,White,QFN0007,5
100001,M,40,White,QFN0011,4
100002,M,55,White,QFN0001,1
100002,M,55,White,QFN0002,4
100002,M,55,White,QFN0003,3
100002,M,55,White,QFN0004,4
100002,M,55,White,QFN0007,5
100002,M,55,White,QFN0011,6
etc
105000,M,48,White,QFN0001,5
105000,M,48,White,QFN0002,4
105000,M,48,White,QFN0003,6
105000,M,48,White,QFN0005,3
105000,M,48,White,QFN0007,4
105000,M,48,White,QFN0011,5

We would like the resulting table to look like the following:

ExamID,sex,age,race,QFN0001,QFN0002,QFN0003,QFN000 5,QFN0007,QFN0011
100001,M,40,White,0,5,6,3,5,4
100002,M,55,White,1,4,3,4,5,6
etc
105000,M,48,White,5,4,6,3,4,5
Any suggestions appreciated.

Gregory S. Moy
Information Processing Consultant
EpiSense Research Program
Department of Ophthalmology & Visual Sciences
University of Wisconsin - Madison

[Simon Hayes]

"Gregory S Moy" <mo*@epi.ophth.wisc.edu> wrote in message
news:MP************************@news.doit.wisc.edu ...

I'm playing around with the following tables and need some advice.

<snip>

You seem to be looking for a crosstab:

http://www.aspfaq.com/show.asp?id=2462

If you need anything more complex, it would probably be worth looking at a
reporting tool instead of a pure TSQL solution. If you still aren't clear on
how to write your query, I suggest you post CREATE TABLE and INSERT
statements to set up a test case - that makes it easy for someone else to
copy and paste into QA.

Simon