SQL pivoting out unknown number of row values as columns

Question

I am loading a survey into a database SQL Server (2016) and unfortunately the only way I can retrieve the data is through JSON. I think I am having a brain fart as I'm having trouble figuring out what I think is simple. I have a table with a few attributes/IDs and then the last 2 fields are the survey question and answer as shown below. Through SQL, how could I accomplish the following?

-------------------------------
| Id | Question  | Answer     |
-------------------------------
| 1  | Q1        | A1         |
| 2  | Q2        | A2         |
| 3  | Q3        | A3         |
-------------------------------

I need to get it into the following format. Basically turning the questions into columns. Is this possible? Something to note, the number of rows is unknown, so there could be 50 survey questions or so. It's variable.

-------------------------------
ID | Q1  | Q2 | Q3  |
-------------------------------
 1 | A1  | A2 | A3  |

Thank you,

Possibly duplicate of https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Daniel Brughera, Feb 13 '19 at 19:52
Are you sure it's not some other column than ID that should have the answers on 1 line? ID is often the primary key of a table. — LukStorms, Feb 13 '19 at 20:02
Possible duplicate of [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — PM 77-1, Feb 13 '19 at 20:05

score 2 · Answer 1 · answered Feb 13 '19 at 20:30

You could use a dynamic Sql to pivot with unknown column names.

example:

-- Test table
CREATE TABLE YourTable
(
    ID int primary key identity(1,1),
    OtherId int not null,
    Question varchar(30) not null,
    Answer varchar(30) not null
);

-- Sample Data
insert into YourTable (OtherId, Question, Answer) values
 (1, 'Q1', 'A1'), (1, 'Q2', 'A2'), (1, 'Q3', 'A3'), 
 (2, 'Q1', 'A4'), (2, 'Q2', 'A5'), (2, 'Q3', 'A6');

Query

DECLARE @Cols  AS NVARCHAR(MAX);
DECLARE @DynSql AS NVARCHAR(MAX);

SELECT @Cols = CONCAT(@Cols + ', ', QUOTENAME(Question))
FROM YourTable
GROUP BY Question
ORDER BY Question;

SET @DynSql = N'SELECT * 
FROM  
(
    SELECT OtherId, Question, Answer
    FROM YourTable
) src
PIVOT 
(
    MAX(Answer) 
    FOR Question IN (' + @Cols + N')
) pvt
ORDER BY OtherId';

-- SELECT @DynSql AS DynSql;
EXECUTE sp_executesql @DynSql;

Result:

OtherId Q1  Q2  Q3
1       A1  A2  A3
2       A4  A5  A6

Been trying to figure this one out for ages. Thanks @LukStorms — Hilary, Feb 11 '22 at 09:46
@Hilary Thx. And if you're going to use criteria in the Dynamic Sql, then don't just concatinate them to the string. Use parameters instead. Example [here](https://stackoverflow.com/a/70943740/4003419) — LukStorms, Feb 11 '22 at 10:47

SQL pivoting out unknown number of row values as columns

1 Answers1

Linked