mardi 27 janvier 2015

Pivoting table with duplicates in SQL

I have a table of survey responses, and the responses by each subject for each question are listed out in rows, not columns.


Here is an example of the table:


Example of the input table


After searching, I was able to pivot it using this:



SELECT id, question, answer
MAX (CASE WHEN [question] = 'do you like cheese' THEN [answer] ELSE NULL END) AS [q1],
MAX (CASE WHEN [question] = 'what type of cheese' THEN [answer] ELSE NULL END) AS [q2]
MAX (CASE WHEN [question] = 'where do you buy your cheese' THEN [answer] ELSE NULL END) AS [q3]
FROM CheeseTable
GROUP BY id


However, as you can see from the table in the link above, there are 2 entries with id = 1 that have different answers.


Basically, they are the same person, but they filled the survey twice (i.e., follow-up). When I use GROUP BY it only pivots one of the duplicate ids. Is there a way to also pivot and include the duplicate ids?


This is the output I want to get:


Example of desired output


Aucun commentaire:

Enregistrer un commentaire