Using UNPIVOT and column names for field values

Question

The context is online assessment responses. The source data I have to work with is one record per Participant ( test taker ) with over 140 columns representing the answer value for each question. The column name would correspond to the "ItemCode" of the specific question.

I want to move this data into the following table

ResponseItems
    ParticipantID
    Form   ( 1 or 2 depending on ParticipantID )
    ItemCode   ( Name of the Column )
    ItemDesc   ( pulled from Codebook on ItemCode and Form
    AnswerValue

I can attack this with a large Union statement but it does not scale to 140 columns wide.

But as an example, here is code that works for the 1st 3 Item Codes or Columns of answers:

Select ar.ParticipantID, ar.Form, c.ItemCode, c.ItemDesc, ar.SJT_01 as 'AnswerValue'
From dbo.Responses ar
Inner Join dbo.CodeBook c on c.ItemCode = 'SJT_01'
    and c.Form = ar.Form

UNION ALL
Select ar.ParticipantID, ar.Form, c.ItemCode, c.ItemDesc, ar.SJT_02 as 'AnswerValue'
From dbo.Responses ar
Inner Join dbo.CodeBook c on c.ItemCode = 'SJT_02'
    and c.Form = ar.Form

UNION ALL
Select ar.ParticipantID, ar.Form, c.ItemCode, c.ItemDesc, ar.SJT_03 as 'AnswerValue'
From dbo.Responses ar
Inner Join dbo.CodeBook c on c.ItemCode = 'SJT_03'
    and c.Form = ar.Form

If I had 100 Test Takers and 3 Item Codes ( 3 columns with answers I want to capture, I am taking a 100 record table and creating 300 records.

I have studied the UNPIVOT command but I can't seem to get it to work. Any suggestions would be greatly appreciated.

Source data with 10 test takers ... results desired are

ParticipantID, Form, ItemCode, AnswerValue

AICPAPSS003    1    SJT_01   5
AICPAPSS003    1    SJT_02   1
AICPAPSS003    1    SJT_03   3
AICPAPSS007    1    SJT_01   3
AICPAPSS007    1    SJT_02   1
AICPAPSS007    1    SJT_03   5

etc... ( a total of 30 records would be created )

Answer 1

Here you go - I think this is what you were after. If you have 140+ columns you might want to use some dynamic SQL to generate the final query. There are heaps of examples on SO.

Setup

IF(OBJECT_ID('Tempdb..#Test')) IS NOT NULL 
DROP TABLE  #Test;


SELECT * INTO #Test FROM (VALUES
('AICPAPSS003',1,5, 1, 3),
('AICPAPSS007',1,3, 1, 5),
('AICPAPSS012',1,2, 1, 4),
('AICPAPSS016',1,3, 2, 5),
('AICPAPSS019',1,1, 2, 5),
('AICPAPSS024',1,3, 2, 4),
('AICPAPSS025',1,1, 1, 4),
('AICPAPSS032',1,1, 2, 4),
('AICPAPSS033',1,3, 4, 5),
('AICPAPSS034',1,1, 2, 4)) A (ParticipantID, Form, SJT_01, SJT_02, SJT_03);

Query

SELECT ParticipantID, Form, ItemCode, Value 
FROM #Test t
UNPIVOT 
(
    Value FOR ItemCode IN (SJT_01, SJT_02, SJT_03)
) u;

Results

ParticipantID Form        ItemCode                                                                                                                         Value
------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- -----------
AICPAPSS003   1           SJT_01                                                                                                                           5
AICPAPSS003   1           SJT_02                                                                                                                           1
AICPAPSS003   1           SJT_03                                                                                                                           3
AICPAPSS007   1           SJT_01                                                                                                                           3
AICPAPSS007   1           SJT_02                                                                                                                           1
AICPAPSS007   1           SJT_03                                                                                                                           5
AICPAPSS012   1           SJT_01                                                                                                                           2
AICPAPSS012   1           SJT_02                                                                                                                           1
AICPAPSS012   1           SJT_03                                                                                                                           4
AICPAPSS016   1           SJT_01                                                                                                                           3
AICPAPSS016   1           SJT_02                                                                                                                           2
AICPAPSS016   1           SJT_03                                                                                                                           5
AICPAPSS019   1           SJT_01                                                                                                                           1
AICPAPSS019   1           SJT_02                                                                                                                           2
AICPAPSS019   1           SJT_03                                                                                                                           5
AICPAPSS024   1           SJT_01                                                                                                                           3
AICPAPSS024   1           SJT_02                                                                                                                           2
AICPAPSS024   1           SJT_03                                                                                                                           4
AICPAPSS025   1           SJT_01                                                                                                                           1
AICPAPSS025   1           SJT_02                                                                                                                           1
AICPAPSS025   1           SJT_03                                                                                                                           4
AICPAPSS032   1           SJT_01                                                                                                                           1
AICPAPSS032   1           SJT_02                                                                                                                           2
AICPAPSS032   1           SJT_03                                                                                                                           4
AICPAPSS033   1           SJT_01                                                                                                                           3
AICPAPSS033   1           SJT_02                                                                                                                           4
AICPAPSS033   1           SJT_03                                                                                                                           5
AICPAPSS034   1           SJT_01                                                                                                                           1
AICPAPSS034   1           SJT_02                                                                                                                           2
AICPAPSS034   1           SJT_03                                                                                                                           4

(30 row(s) affected)