-1

I have a single row of data (name, address, question_ID_1, Answer_1, question_ID_2, Answer_2, etc) and wish to unpivot this so that it reads: 

row1: name, address, question_ID_1, Answer_1;
row2: name, address, question_ID_2, Answer_2;
row3: name, address, question_ID_3, Answer 3; etc.

I have managed to get a variation of this but not the final result. I have concatenated the Question and Answer fields and dont mind unpivoting using this - so...

row1: name, address, QA1;
row2: name, address, QA2;
row3:  name, address, QA3 etc.

I have a variable number of columns depending on the import too.

I am importing a flat file (using SSIS) into a SQLServer 2005 table,and then transforming the data into the chosen format using a stored procedure. Any code you can supply would be a great help.

cha
  • 10,301
  • 1
  • 18
  • 26
user3735855
  • 144
  • 2
  • 20

1 Answers1

0

You can use UNPIVOT to turn the columns into rows.

SELECT name, address, question, answer
FROM questions
UNPIVOT
  (question for q in (question_ID_1, question_ID_2)) as q
UNPIVOT 
  (answer for a in (answer_1, answer_2)) as a
WHERE RIGHT(q,1) = RIGHT(a,1)

Here's a SqlFiddle to show this: http://sqlfiddle.com/#!3/e6919/3

Paul Kearney - pk
  • 5,435
  • 26
  • 28