問題描述

我正在開發一個允許配置問題和答案的應用程序.目前最多可以有 20 個答案，但可能更少.

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

我的結構如下:

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

答案

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

因此在問題表中的 AnswerField Answer01 映射到 Answers 表中的 Answer01 列

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:

對于表單 ID 1 &記錄 ID 1:

For form ID 1 & record ID 1:

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

然后對于表單 id 2 &記錄 ID 3:

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

這顯然是不對的.

誰能建議如何在 SQL Server 存儲過程中完成此操作?

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

推薦答案

首先，您的 Answers 表設計得非常糟糕.該表未規范化，這會在您想要返回數據時給您帶來問題.如果可能，您需要重構該表.

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.

UNPIVOT 會將您的列轉換為行.逆透視代碼將是:

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;

請參閱SQL Fiddle with Demo.這給出了一個結果:

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;

請參閱SQL Fiddle with Demo.這給出了一個結果:

See SQL Fiddle with Demo. This gives a result:

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

這篇關于如何在動態查詢中將行值連接到列名的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！