CODEWITHSUNDEEP
sqlpivotsql-server-2016
user3494110
user3494110

Reputation: 427

SQL pivoting out unknown number of row values as columns

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,

Upvotes: 1

Views: 2986

Answers (1)

LukStorms
LukStorms

Reputation: 29667

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

Upvotes: 4

Related Questions