将多列拆分为多行

Question

我有一张具有这种结构的表。

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

我无法确定使用什么SQL查询可以获得这样的结果集：

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

我试图将三列拆分为三个单独的行。这是可能的吗？

- Sandro

Answer 1

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中也适用。我利用问题的相似性来创建QuestionName列，但是当然，这将适用于不同的问题名称。请注意，如果您的问题列表很长或问题名称很长，则可以尝试在X表中使用2个列，一个用于问题编号，另一个用于问题名称。或者，如果您已经有一个包含问题列表的表，请CROSS JOIN到该表。如果某些问题为NULL，则最简单的方法是将上面的查询放入CTE或派生表中，然后添加WHERE Response IS NOT NULL。

- ErikE

---

我使用这个代码出现了“列questionname不存在”的错误。 - hedgedandlevered

不，我的查询没问题。只有在尝试在查询中的其他地方引用别名“QuestionName”时才会出现该错误。将此查询放入派生表中并从中选择，然后您可以对其进行条件设置。您正在使用SQL Server，对吗？ - ErikE

哦，我使用的是普通的SQL。没有看到sql-server标签。 谢谢，最后通过8kb和你的答案解决了问题。 - hedgedandlevered

对于其他的DBMS，您需要使用'AnswerToQuestion' + X.Which AS QuestionName。 - ErikE

另一种变体的CROSS JOIN也可以工作，并且在我看来看起来更加简洁：CROSS JOIN (VALUES ('1'), ('2'), ('3')) X (Which)。 - Jason Roman

@JasonRoman 的确，我也更喜欢那种语法……如果使用的是高于 SQL Server 2000 的版本的话。 :) - ErikE

Answer 2

根据Itzik Ben-Gan在《深入解析Microsoft SQL Server 2008：T-SQL查询》中的说法，当对表进行非标准化操作时，SQL Server会经过三个步骤：

1. 生成副本
2. 提取元素
3. 删除NULL行

步骤1：生成副本 为每个需要非标准化的列创建一个虚拟表，并复制原始表中的每一行。 此外，将列名的字符串存储在新列中（称之为QuestionName列）。*注意：我将其中一个列的值修改为NULL以展示整个过程。

UserID  UserName  AnswerTo1 AnswerToQ2 AnswerToQ3 QuestionName
1       John      1         0          1          AnswerToQuestion1
1       John      1         0          1          AnswerToQuestion2
1       John      1         0          1          AnswerToQuestion3
2       Mary      1         NULL       1          AnswerToQuestion1
2       Mary      1         NULL       1          AnswerToQuestion2
2       Mary      1         NULL       1          AnswerToQuestion3

步骤2：提取元素

然后创建另一个表格，为源列中与QuestionName列中的字符字符串值对应的每个值创建新行。该值存储在一个新列中（称之为响应列）。

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

步骤三：删除空行

该步骤筛选掉响应列中包含 null 值的任何行。换句话说，如果任何一个问题回答列具有 null 值，则不会表示为未展开的行。

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

如果您按照以下步骤操作，您可以：

1. 使用CROSS JOIN将表中的所有行与每个AnswerToQuestion列名进行连接，以获取行副本
2. 根据匹配源列和QuestionName填充Response列
3. 删除NULL值，以获得不使用UNPIVOT的相同结果。

以下是一个示例：

DECLARE @t1 TABLE (UserID INT, UserName VARCHAR(10), AnswerToQuestion1 INT, 
  AnswertoQuestion2 INT, AnswerToQuestion3 INT
) 

INSERT @t1 SELECT 1, 'John', 1, 0, 1 UNION ALL SELECT 2, 'Mary', 1, NULL, 1 

SELECT
  UserID,
  UserName,
  QuestionName,
  Response
FROM (
  SELECT
    UserID,
    UserName,
    QuestionName,
    CASE QuestionName
      WHEN 'AnswerToQuestion1' THEN AnswerToQuestion1
      WHEN 'AnswerToQuestion2' THEN AnswertoQuestion2
      ELSE AnswerToQuestion3 
    END AS Response 
  FROM @t1 t1
      CROSS JOIN (
        SELECT 'AnswerToQuestion1' AS QuestionName
        UNION ALL SELECT 'AnswerToQuestion2'
        UNION ALL SELECT 'AnswerToQuestion3'
      ) t2
    ) t3
WHERE Response IS NOT NULL

- 8kb

---

非常感谢您的高质量回复。我真的很喜欢您的回答，因为它是最详细的，但是我可能不得不把答案给Emtucifor，因为他比您早回答了几个小时。谢谢！ - Sandro

Answer 3

假设使用的是 SQL Server 2005+，您可以使用 UNPIVOT。

;with YourTable as
(
SELECT 1 UserID,'John' UserName,1 AnswerToQuestion1,0 AnswerToQuestion2,1 AnswerToQuestion3 
UNION ALL
SELECT 2, 'Mary', 1, 1, 0
)
SELECT UserID, UserName, QuestionName, Response
FROM YourTable
UNPIVOT
   (Response FOR QuestionName IN 
      (AnswerToQuestion1, AnswerToQuestion2,AnswerToQuestion3)
)AS unpvt;

- Martin Smith

---

抱歉回复晚了，感谢您的回复。除了使用UNPIVOT运算符，还有其他替代方案吗？ - Sandro