How to join row values to column names in a dynamic query

Question

I am developing an application that allows configurable questions and answers. Currently there can be up to 20 answers, but possibly less.

The structure I have is as follows:

Questions

+----+--------+--------------+-------------+
| ID | FormId | QuestionText | AnswerField |
+----+--------+--------------+-------------+
|  1 |      1 | Name         | Answer01    |
|  2 |      1 | Address      | Answer02    |
|  3 |      1 | Phone        | Answer03    |
|  4 |      1 | Email        | Answer04    |
|  5 |      2 | First Name   | Answer01    |
|  6 |      2 | Surname      | Answer02    |
+----+--------+--------------+-------------+

Answers

+----+--------+----------+------------+--------------+--------------+----------------+----------+----------+
| ID | FormId | RecordId |  Answer01  |   Answer02   |   Answer03   |    Answer04    | Answer05 | Answer06 |
+----+--------+----------+------------+--------------+--------------+----------------+----------+----------+
|  1 |      1 |        1 | Bob Smith  | Bobs Address | 01234 111222 | bob@smith.com  | Null     | Null     |
|  2 |      1 |        2 | Joe Bloggs | Joes Address | 04321 333444 | joe@bloggs.com | Null     | Null     |
|  3 |      2 |        3 | David      | Jones        | Null         | Null           | Null     |          |
+----+--------+----------+------------+--------------+--------------+----------------+----------+----------+

So in the Questions table AnswerField Answer01 maps to the Answer01 column in the Answers table

What I would like to do is get a result set that looks something like:

For form ID 1 & record ID 1:

+--------------+---------------+
| QuestionText |    Answer     |
+--------------+---------------+
| Name         | Bob Smith     |
| Address      | Bobs Address  |
| Phone        | 01234 111222  |
| Email        | bob@smith.com |
+--------------+---------------+

Then for form id 2 & record id 3:

+--------------+---------+
| QuestionText | Answer  |
+--------------+---------+
| First Name   | David   |
| Surname      | Jones   |
+--------------+---------+

I have tried using a pivot table:

SELECT QuestionText, Answer01, Answer02, Answer03, Answer04
FROM (
    SELECT DISTINCT Q.AnswerField, Q.QuestionText, Q.ID, A.Answer01, A.Answer02, A.Answer03, A.Answer04
    FROM Questions Q
    INNER JOIN Answers A ON A.FormId= Q.FormId
    WHERE A.ID = 17
) 
AS src
PIVOT (MAX(question_id) FOR Answer IN(answer_01, answer_02, answer_03, answer_04)) AS pvt

But this repeats the answers in all columns:

+--------------+-----------+--------------+--------------+---------------+
| QuestionText | Answer01  |   Answer02   |   Answer03   |   Answer04    |
+--------------+-----------+--------------+--------------+---------------+
| Name         | Bob smith | Bobs Address | 01234 111222 | bob@smith.com |
| Address      | Bob smith | Bobs Address | 01234 111222 | bob@smith.com |
| Phone        | Bob smith | Bobs Address | 01234 111222 | bob@smith.com |
| Email        | Bob smith | Bobs Address | 01234 111222 | bob@smith.com |
+--------------+-----------+--------------+--------------+---------------+

Which obviously isn't right.

Can anyone suggest how this might be done in a SQL Server stored procedure please?

Answer 1

First things first, your Answers table is terribly designed. That table is not normalized which is going to cause you problems when you want to return data. If possible, you need to restructure that table.

If you cannot redesign the table, then you will have to unpivot the answers table to be able to easily join the answers to the the questions.

An UNPIVOT will take your columns and convert them into rows. The unpivot code will be:

select formid, RecordId, answer, answercol
from answers a
unpivot
(
  answer
  for answerCol in ([Answer01], [Answer02], [Answer03], 
                    [Answer04], [Answer05], [Answer06])
) unpiv;

See SQL Fiddle with Demo. This gives a result:

| FORMID | RECORDID |         ANSWER | ANSWERCOL |
--------------------------------------------------
|      1 |        1 |      Bob Smith |  Answer01 |
|      1 |        1 |   Bobs Address |  Answer02 |
|      1 |        1 |   01234 111222 |  Answer03 |
|      1 |        1 |  bob@smith.com |  Answer04 |

Once the data is in rows, then you can join the questions table to return the result that you want:

select q.questiontext, d.answer
from questions q
inner join
(
  select formid, RecordId, answer, answercol
  from answers a
  unpivot
  (
    answer
    for answerCol in ([Answer01], [Answer02], [Answer03], 
                      [Answer04], [Answer05], [Answer06])
  ) unpiv
) d
  on q.AnswerField = d.answercol
  and q.formid = d.formid
where d.recordid = 1;

See SQL Fiddle with Demo. This gives a result:

| QUESTIONTEXT |        ANSWER |
--------------------------------
|         Name |     Bob Smith |
|      Address |  Bobs Address |
|        Phone |  01234 111222 |
|        Email | bob@smith.com |