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

Campion

can't join to result set unless you use dynamic sql. easier to use the select statement to make your 70 select statements and then copy those with an editor. This will save you some typing. – Hinz

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 |

Varve

I've upvoted this because it's excellent advice: UNPIVOT is much neater than CROSS APPLY and is intended for exactly this situation. However, this doesn't really answer the question as the OP wants to avoid writing out SQL to cover 70 columns; this still requires the columns to be typed out. (I accept there are shortcuts for this, e.g. dragging from the Columns folder in Object Explorer.) – Theomorphic

I have also explored this function as well but I got a few tables in the structure like this and the number of questions are variable. I wonder if there's a way that I dont need to type Q1-Q70 or so on? Thanks for your help. – Campion

@Campion my alternative answer should give you a way to do that, did you see it? – Theomorphic

This Query is cool but not supported in MariaDB – Tonyatonye

Answer 2

If you need to perform this kind of operation to lots of similar tables that have differing numbers of columns, an UNPIVOT approach alone can be tiresome because you have to manually change the list of columns (Q1,Q2,Q3,etc) each time.

The CROSS APPLY based query in the question also suffers from similar drawbacks.

The solution to this, as you've guessed, involves using meta-information maintained by the server to tell you the list of columns you need to operate on. However, rather than requiring some kind of join as you suspect, what is needed is Dynamic SQL, that is, a SQL query that creates another SQL query on-the-fly.

This is done essentially by concatenating string (varchar) information in the SELECT part of the query, including values from columns which are available in your FROM (and join) clauses.

With Dynamic SQL (DSQL) approaches, you often use system metatables as your starting point. INFORMATION_SCHEMA exists in some SQL Server versions, but you're better off using the Object Catalog Views for this.

A prototype DSQL solution to generate the code for your CROSS APPLY approach would look something like this:

-- Create a variable to hold the created SQL code
-- First, add the static code at the start:
declare @SQL varchar(max) = 
'  SELECT t.[SurveyID]
  , x.question
  , x.Answer
  FROM tbl t
  CROSS APPLY 
  (
'

-- This syntax will add to the variable for every row in the query results; it's a little like looping over all the rows. 
select @SQL +=
     'select ' + cast(C.column_id as varchar)
    + ' as QuestionNumber, ''' + C.name 
    + ''' as Question , t.' + C.name
    + ' As Answer union all
    '
from sys.columns C
inner join sys.tables T on C.object_id=T.object_id
where T.name = 'MySurveyTable'

-- Remove final "union all", add closing bracket and alias
set @SQL = left(@SQL,len(@SQL)-10) + ') x'

print @SQL

-- To also execute (run) the dynamically-generated SQL
-- and get your desired row-based output all at the same time, 
-- use the EXECUTE keyword (EXEC for short)

exec @SQL

A similar approach could be used to dynamically write SQL for the UNPIVOT approach.

Theomorphic

Note I've written this in something of a hurry without ability to test on an actual SQL Server box; if anyone spots typos or minor bugs please do correct them, with my apologies ;-) – Theomorphic