Include Column name in SQL Unpivot

I have an unnormalized data set I need to normalize for reporting purposes.

    SELECT [Id]
      ,[Timestamp]
      ,[Question1]
      ,[Question2]
      ,[Question3]
      ,[Question4]
  FROM [COS].[dbo].[Sheet2$]

I successfully unpivoted the data using this:

    SELECT Id,
       Result
FROM   (SELECT Id,
               Cast([Question1] AS VARCHAR(255)) AS Q1,
               Cast([Question2] AS VARCHAR(255)) AS Q2,
               Cast([Question3] AS VARCHAR(255)) AS Q3,
               Cast([Question4] AS VARCHAR(255)) AS Q4
        FROM   Sheet2$) AS A
       UNPIVOT ( result
               FOR questions IN ( Q1,
                                  Q2,
                                  Q3,
                                  Q4 ) ) AS b

How can unpivot column headers Question1, Question2, Question3, Question4 as a third column in my unpivot query, like this?

Answer

Your syntax looks like SQL Server. If so, just use a APPLY:

SELECT s.id, v.*
FROM Sheet2$ s CROSS APPLY
     (VALUES ('Question1', s.Question1),
             ('Question2', s.Question2),
             ('Question3', s.Question3),
             ('Question4', s.Question4)
     ) v(question, result);