如何在动态查询中将行值连接到列名

2021-09-14 00:00:00 sql sql-server unpivot

我正在开发一个允许配置问题和答案的应用程序.目前最多可以有 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 |

相关文章