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

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.