Join tables by column names, convert string to column name

Question

I have a table which store 1 row per 1 survey.
Each survey got about 70 questions, each column present 1 question

SurveyID  Q1, Q2   Q3   .....
1         Yes Good Bad  ......

I want to pivot this so it reads

SurveyID Question  Answer
1        Q1        Yes
1        Q2        Good
1        Q3        Bad
...      ...       .....

I use {cross apply} to acheive this

   SELECT t.[SurveyID]
  , x.question
  , x.Answer
  FROM tbl t
  CROSS APPLY 
  (
    select 1 as QuestionNumber, 'Q1' as Question , t.Q1 As Answer union all
    select 2 as QuestionNumber, 'Q2' as Question , t.Q2 As Answer union all
    select 3 as QuestionNumber, 'Q3' as Question , t.Q3 As Answer) x

This works but I dont want to do this 70 times so I have this select statement

    select ORDINAL_POSITION
    , COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = mytable

This gives me the list of column and position of column in the table. So I hope I can somehow join 2nd statement with the 1st statement where by column name. However I am comparing content within a column and a column header here. Is it doable? Is there other way of achieving this?

Hope you can guide me please?

Thank you

Answer 1

Instead of Cross Apply you should use UNPIVOT for this query....

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE Test_Table(SurveyID  INT, Q1 VARCHAR(10)
                                     , Q2 VARCHAR(10),  Q3 VARCHAR(10), Q4 VARCHAR(10))

INSERT INTO Test_Table VALUES
 (1 , 'Yes',  'Good' ,  'Bad',  'Bad')  
,(2 ,  'Bad',  'Bad' , 'Yes' ,  'Good')

Query 1:

SELECT SurveyID
      ,Questions
      ,Answers
FROM Test_Table t 
 UNPIVOT ( Answers FOR Questions IN (Q1,Q2,Q3,Q4))up

Results:

| SurveyID | Questions | Answers |
|----------|-----------|---------|
|        1 |        Q1 |     Yes |
|        1 |        Q2 |    Good |
|        1 |        Q3 |     Bad |
|        1 |        Q4 |     Bad |
|        2 |        Q1 |     Bad |
|        2 |        Q2 |     Bad |
|        2 |        Q3 |     Yes |
|        2 |        Q4 |    Good |