当前位置: 首页 > 面试题库 >

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

薛寒
2023-03-14
问题内容

我正在开发一个允许配置问题和答案的应用程序。目前最多可以有20个答案,但可能更少。

我的结构如下:

问题

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

因此,在Questions表中AnswerField Answer01映射到Answers表中的Answer01列

我想做的就是得到一个看起来像这样的结果集:

对于表格ID 1和记录ID 1:

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

然后对于表单ID 2和记录ID 3:

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

我尝试使用数据透视表:

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

但这在所有列中都重复了答案:

+--------------+-----------+--------------+--------------+---------------+
| 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存储过程中如何做到这一点吗?


问题答案:

首先,您的Answers桌子经过精心设计。该表未规范化,当您要返回数据时会给您带来麻烦。如果可能,您需要重组该表。

如果您不能重新设计表格,那么您将不得不取消对答案表的透视,以便能够轻松地将答案与问题结合起来。

UNPIVOT将获取您的列并将其转换为行。不可更改的代码将是:

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

请参阅带有演示的SQL Fiddle。结果如下:

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

数据放入行后,您可以加入问题表以返回所需的结果:

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。结果如下:

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


 类似资料:
  • 问题内容: MySQL是否可以将列转换为行,从而动态添加行所需的列数。我认为我的问题可能与数据透视表有关,但是我不确定,除了给出以下示例之外,我不知道如何构造此问题。 给定两个表A和B,它们看起来像 表A 我想编写一个如下查询: 结果表 基本上,我想将表B中的每一行变成结果表中的一列。如果有一个新条目被添加到表B中,id = 1,那么我希望结果表自动扩展一列以容纳这个额外的数据点。 问题答案: 您

  • 问题内容: 可以构造SQL来连接多行中的列值吗? 以下是一个示例: 表A 表B SQL的输出应为- 因此,基本上,输出表的Desc列是表B的SEQ值的串联? 对SQL有帮助吗? 问题答案: 有几种方法取决于您使用的版本-请参见有关字符串聚合技术的oracle文档。一种很常见的用法是: 然后加入以挑选您想要的。 注意: 开箱即用,仅适用于列。

  • 在HashMap中检索组键后,如何执行联接查询,该查询只显示那些详细信息,其中成员是该特定用户。如果这个结构是错误的,请帮助我。 结构:

  • 问题内容: 如何动态传递变量参数 TypeError:必须为str,而不是int 尽管我转换为字符串有任何想法,但遇到了以上错误? 有没有其他的方式来传递参数? 问题答案: 通过通过关键字arguments提供参数来使用参数化sql 。数据库适配器将为您完成参数的正确引用,并且代码将较不容易受到SQL注入攻击。(有关不正确引用的非参数化sql可能引起您的麻烦的类型,请参阅Little Bobby

  • 问题内容: 我正在处理数据透视表查询。架构如下 Sno,名称,地区 同一名称可能在许多地区出现,例如以样本数据为例 如您所见,我有一组4个不同的地区(CA,JB,MN,LP)。现在,我想通过将名称映射到区域来获取为其生成的数据透视表 我为此写了以下查询 但是,地区可能会增加,在这种情况下,我将不得不再次手动编辑查询并向其中添加新的地区。 我想知道是否存在一个查询,该查询可以动态获取不同地区的名称并

  • 我已经找到了解决方案(我认为),我将要求在甲骨文和SQL服务器上的问题,但似乎无法将其转化为Postgres解决方案。我正在使用Postgres 9.3.6。 这个想法是能够生成有关表内容的“元数据”以用于分析目的。这只能通过为每列运行查询来完成 (AFAIK),以便找出,比如说......最小值/最大值/计数值等。为了自动执行该过程,最好先由数据库生成查询,然后执行。 使用示例表,我能够使用以下