Join tables by column names, convert string to column name

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

Solution

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 |