Split Multiple Columns into Multiple Rows

Question

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:

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?

Answer 1

Assuming SQL Server 2005+ you can use 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;

Answer 2

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.

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.

Answer 3

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

1. Generate copies
2. Extract elements
3. Remove rows with NULLs

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.

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

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

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

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.

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. Populate the Response column based on the matching the source column and QuestionName
3. Remove the NULLs to get the same results without using UNPIVOT.

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