問(wèn)題描述

我正在開(kāi)發(fā)一個(gè)允許配置問(wèn)題和答案的應(yīng)用程序.目前最多可以有 20 個(gè)答案，但可能更少.

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

我的結(jié)構(gòu)如下:

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

因此在問(wèn)題表中的 AnswerField Answer01 映射到 Answers 表中的 Answer01 列

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

我想做的是得到一個(gè)看起來(lái)像這樣的結(jié)果集:

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

對(duì)于表單 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 |
+--------------+---------------+

然后對(duì)于表單 id 2 &記錄 ID 3:

Then for form id 2 & record id 3:

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

我曾嘗試使用數(shù)據(jù)透視表:

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

但這會(huì)重復(fù)所有列中的答案:

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

這顯然是不對(duì)的.

誰(shuí)能建議如何在 SQL Server 存儲(chǔ)過(guò)程中完成此操作?

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

推薦答案

首先，您的 Answers 表設(shè)計(jì)得非常糟糕.該表未規(guī)范化，這會(huì)在您想要返回?cái)?shù)據(jù)時(shí)給您帶來(lái)問(wèn)題.如果可能，您需要重構(gòu)該表.

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.

如果您無(wú)法重新設(shè)計(jì)表格，那么您將不得不取消旋轉(zhuǎn)答案表格，以便能夠輕松地加入問(wèn)題的答案.

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 會(huì)將您的列轉(zhuǎn)換為行.逆透視代碼將是:

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;

請(qǐng)參閱SQL Fiddle with Demo.這給出了一個(gè)結(jié)果:

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 |

一旦數(shù)據(jù)成行，就可以加入問(wèn)題表，返回你想要的結(jié)果:

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;

請(qǐng)參閱SQL Fiddle with Demo.這給出了一個(gè)結(jié)果:

See SQL Fiddle with Demo. This gives a result:

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

這篇關(guān)于如何在動(dòng)態(tài)查詢(xún)中將行值連接到列名的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！