Inserting four columns into one

Question

Good morning,

I have a table TestSeed that stores a multiple choices test with the following structure:

QNo  QText  QA1  QA2  QA3  QA4

It already contains data.

I would like to move some of the columns to a temp table with the following structure:

QNo  QA

Where QNo will store the question number from the first table and QA will store QA1, QA2, QA3 and QA4 over four rows of data.

I am trying to do it in a SQL stored procedure. And it got down to the following situation:

I want to create a nested loop where I can go through the TestSeed table rows in the outer loop and then go through the four QA fields and insert them in the inner loop.

So my code will look something like this:

Declare @TempAnswers as table
(
    [QNo] int,
    [QAnswer] [nvarchar](50) NULL,
)

DECLARE @QNO int
DECLARE QROW CURSOR LOCAL FOR select QNo from @TempSeed 

OPEN QROW
FETCH NEXT FROM QROW into @QNO
WHILE @@FETCH_STATUS = 0
BEGIN


DECLARE @i INT
SET @i = 1


WHILE (@i <=4)

Begin



    insert into @TempAnswers
(
    [QNo],
    [QAnswer] 
)

select QNo, 'QA'+@i --This is the part I need
from @TempSeed

SET @i = @i +1

END


    FETCH NEXT FROM QROW into @QNO
END

CLOSE IDs
DEALLOCATE IDs

So I guess my question is: can I use a concatenated string to refer to a column name in SQL? and if so how?

I am sort of a beginner. I would appreciate any help I can.

Answer 1

No need for loop, you can simply use the UNPIVOT table operator to do this:

INSERT INTO temp
SELECT
  QNO, 
  val
FROM Testseed AS t
UNPIVOT
(
  val
  FOR col IN([QA1], [QA2], [QA3], [QA4])
) AS u;

For example, if you have the following sample data:

| QNO | QTEXT | QA1 | QA2 | QA3 | QA4 |
|-----|-------|-----|-----|-----|-----|
|   1 |    q1 |   a |   b |   c |   d |
|   2 |    q2 |   b |   c |   d |   e |
|   3 |    q3 |   e |   a |   b |   c |
|   4 |    q4 |   a |   c |   d |   e |
|   5 |    q5 |   c |   d |   e |   a |

The previous query will fill the temp table with:

| QNO | QA |
|-----|----|
|   1 |  a |
|   1 |  b |
|   1 |  c |
|   1 |  d |
|   2 |  b |
|   2 |  c |
|   2 |  d |
|   2 |  e |
|   3 |  e |
|   3 |  a |
|   3 |  b |
|   3 |  c |
|   4 |  a |
|   4 |  c |
|   4 |  d |
|   4 |  e |
|   5 |  c |
|   5 |  d |
|   5 |  e |
|   5 |  a |

• SQL Fiddle Demo

The UNPIVOT table operator, will convert the values of the four columns [QA1], [QA2], [QA3], [QA4] into rows, only one row.

Then you can put that query inside a stored procedure.