将多个列分割为多个行

I have a table with this structure.

我有一张有这种结构的桌子。

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

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

我不知道我会用什么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

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

我试着把这三列分成三行。这是可能的吗?

3 个解决方案

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

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

它在UNPIVOT上表现得同样好(有时更好)，在SQL 2000中也可以使用。

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

我利用了这些问题的相似度来创建问题名称列，当然，这将使用不同的问题名称。

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.

注意，如果您的问题列表很长或问题名称很长，您可以在X表中使用两列，一列用于问题号，一列用于问题名。或者，如果您已经有一个包含问题列表的表，那么交叉连接。如果一些问题是空的，那么最简单的方法是将上述查询放在CTE或派生表中，然后添加响应不为空的地方。

#2

According to Itzik Ben-Gan in Inside Microsoft SQL Server 2008: T-SQL Querying, SQL Server goes through three steps when unpivoting a table:

根据Itzik beno - gan在Microsoft SQL Server 2008中的数据:T-SQL查询，SQL Server在不旋转表时经过三个步骤:

1. Generate copies
2. 生成副本
3. Extract elements
4. 提取的元素
5. Remove rows with NULLs
6. 删除与null行

Step 1: Generate copies

第一步:生成副本

A virtual table is created that has a copy of each row in the orignal table for each column that is being unpivoted. Also, a character string of the column name is stored in a new column (call this the QuestionName column). *Note: I modified the value in one of your columns to NULL to show the full process.

创建了一个虚拟表，其中每个列的orignal表中的每一行都有一个副本。另外，列名称的字符串将存储在一个新列中(称为问号列)。*注意:我将一个列中的值修改为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

Step 2: Extract elements

步骤2:提取元素

Then another table is created that creates a new row for each value from the source column which corresponds to the character string value in the QuestionName column. The value is stored in a new column (call this the Response column).

然后创建另一个表，为源列中的每个值创建一个新行，该列对应于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

Step 3: Remove rows with NULLS

步骤3:使用null删除行

This step filters out any rows that were created with null values in the Response column. In other words, if any of the AnswerToQuestion columns had a null value, it would not be represented as an unpivoted row.

此步骤筛选响应列中使用空值创建的任何行。换句话说，如果任何一个answer - toquestion列都有一个空值，那么它就不会被表示为一个无轴的行。

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

If you follow those steps, you can

如果你遵循这些步骤，你可以

1. CROSS JOIN all rows in the table against each AnswerToQuestion column name to get row copies
2. 将表中的所有行与每个AnswerToQuestion列名交叉连接，以获取行副本
3. Populate the Response column based on the matching the source column and QuestionName
4. 根据源列和问号的匹配填充响应列。
5. Remove the NULLs to get the same results without using UNPIVOT.
6. 在不使用UNPIVOT的情况下，删除null以获得相同的结果。

An example below:

下面一个例子:

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

#3

Assuming SQL Server 2005+ you can use UNPIVOT

假设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;