Skip to content
Advertisement

Pivoting a table of survey data in SQL without PIVOT

I have a table called Responses which contains survey responses using the following structure.

RespondentID    | QuestionID | Text
----------------+------------+--------------------
745000000144003 | 1          | 424847508003102140
745000000144003 | 2          | someone@example.com
745000000144003 | 3          | 10
745000000144003 | 4          | Long text
745000000137035 | 1          | 548470363003102141
745000000137035 | 2          | someone@me.com
745000000137035 | 3          | 9
745000000137035 | 4          | Long text

This is data for two different survey responses. Each survey has 4 questions (QuestionIDs) but the last one (long text) is optional, so some responses only have 3 rows of data. The first question (QuestionID “1”) could also serve as a primary key for a survey response.

I’m trying to pivot the data so each QuestionID is it’s own column and so that each survey response is only one row. I’m using Zoho Analytics which I don’t believe supports PIVOT.

Thanks for any help!

Advertisement

Answer

You really don’t need PIVOT for this (you didn’t tag your database, likely MS SQL server):

Select RespondentId, 
       Max(Case when QuestionId = 1 then [Text] end) Answer1,
       Max(Case when QuestionId = 2 then [Text] end) Answer2,
       Max(Case when QuestionId = 3 then [Text] end) Answer3,
       Max(Case when QuestionId = 4 then [Text] end) Answer4
from mySurvey 
Group by RespondentId;

PS: It has nothing to do with being web based.

7 People found this is helpful
Advertisement