tsql - How to write SQL Pivot

Question

I'm having an issue trying to pivot column data to row data. Here is the table structure:

submission_id itemID, answerValue

There are 21 rows per submission with the same submissionID, the ItemID(question) and answervalue change in each row. So a row would look like this (abcd1234,7857,'YES') (abcd1234, 2226,'34 some Street)

I'm trying to create one row with the submission id with all of the itemid and answervalue for that submissionID. Hope this makes sense.

question from:https://stackoverflow.com/questions/65890352/how-to-write-sql-pivot

Answer 1

Here is a solution with pivot.

Sample data

I added another submissionId to the sample data to have an extra result row.

create table data
(
  submissionId nvarchar(8),
  itemId int,
  answerValue nvarchar(20)
);

insert into data (submissionId, itemId, answerValue) values
('abcd1234', 7857, 'YES'),
('abcd1234', 2226, '34 some Street'),
('abcd5678', 7857, 'NO'),
('abcd5678', 2226, '100 east lane');

Solution

select piv.submissionId,
       [7857] as Question1,
       [2226] as Question2
from data d
pivot (max(d.answerValue) for d.itemId in ([7857], [2226])) piv;

Result

submissionId Question1 Question2
------------ --------- --------------
abcd1234     YES       34 some Street
abcd5678     NO        100 east lane

Fiddle to see things in action.