0

I have a database for our students where we store questions and responses in it. I am trying to build a report on it. There is a question table, and a response table. Both of these tables have a common field, QuizNo. The table structure is similar as follows:

Quiz Table:

QuizNo |   Status
  1       Reviewed
  2       Not Reviewed

Quiz Response Table:

QuizNo  | QuestionID | Response
1             11         Yes
2             13         No
2             11         Yes
1             13         No

The expected result from the query would contain the amount of rows in the Quiz table, in this case 2. Each of the questions ID's and their responses would form a new column, in the same row.

The tabular data being return would look like the following:

                  (11 and 13 are question ID's)

QuizNo  |  Status     |   11   |    13
  1        Reviewed       Yes       No
  2        Not Reviewed   Yes       No

I have tried a few different things, but I can't get the column headers to turn into the question ID's with the responses.

TIA!

user3010406
  • 541
  • 2
  • 8
  • 22
  • You'll want to PIVOT the values of your `QuestionID` into columns, the duplicate has several examples on how to do this. – Taryn Sep 12 '14 at 14:17
  • the duplicate is showing how to transform all the select to columns, I am only trying to transform a subset... – user3010406 Sep 12 '14 at 14:24
  • You only transform the columns that you need in this case the question id. In your case the code would be similar to `pivot( max(Response) for questionid in ([11], [13]))` – Taryn Sep 12 '14 at 14:25
  • So I have to hardcode per question ID? – user3010406 Sep 12 '14 at 14:29
  • If you have a few then yes, if not then you'd use dynamic sql which also is explained in the duplicate. – Taryn Sep 12 '14 at 14:30

0 Answers0