By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,660 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,660 IT Pros & Developers. It's quick & easy.

SQL Transpose Table

P: n/a
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
Jul 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"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
Jul 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.