将多列拆分为多行 [英] Split Multiple Columns into Multiple Rows

问题描述

我有一张采用这种结构的表格.

I have a table with this structure.

UserID  | UserName  | AnswerToQuestion1 | AnswerToQuestion2 | AnswerToQuestion3
1       | John      | 1                 | 0                 | 1
2       | Mary      | 1                 | 1                 | 0

我不知道要使用什么 SQL 查询来获得这样的结果集:

I can't figure out what SQL query I would use to get a result set like this:

UserID  | UserName  | QuestionName      | Response
1       | John      | AnswerToQuestion1 | 1
1       | John      | AnswerToQuestion2 | 0
1       | John      | AnswerToQuestion3 | 1
2       | Mary      | AnswerToQuestion1 | 1
2       | Mary      | AnswerToQuestion2 | 1
2       | Mary      | AnswerToQuestion3 | 0

我正在尝试将三列拆分为三个单独的行.这可能吗?

I'm trying to split the three columns into three separate rows. Is this possible?

推荐答案

SELECT
   Y.UserID,
   Y.UserName,
   QuestionName = 'AnswerToQuestion' + X.Which,
   Response =
      CASE X.Which
      WHEN '1' THEN AnswerToQuestion1
      WHEN '2' THEN AnswerToQuestion2
      WHEN '3' THEN AnswerToQuestion3
      END
FROM
   YourTable Y
   CROSS JOIN (SELECT '1' UNION ALL SELECT '2' UNION ALL SELECT '3') X (Which)

这与 UNPIVOT 的表现同样好(有时更好)，并且也适用于 SQL 2000.

This performs equally well to UNPIVOT (sometimes better) and works in SQL 2000 as well.

我利用问题的相似性创建了 QuestionName 列，但这当然适用于不同的问题名称.

I took advantage of the questions' similarity to create the QuestionName column, but of course this will work with varying question names.

请注意，如果您的问题列表很长或问题名称很长，您可以尝试在 X 表中设置 2 列，一列用于问题编号，另一列用于问题名称.或者，如果您已经有一个包含问题列表的表格，则交叉加入该表格.如果某些问题为 NULL，那么最简单的方法是将上述查询放在 CTE 或派生表中，然后添加 WHERE Response IS NOT NULL.

Note that if your list of questions is long or the question names are long, you might experiment with 2 columns in the X table, one for the question number and one for the question name. Or if you already have a table with the list of questions, then CROSS JOIN to that. If some questions are NULL then easiest is to put the above query in a CTE or derived table and then add WHERE Response IS NOT NULL.

这篇关于将多列拆分为多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！