I have a table Occupation like such:
x
Name | Occupation
-----------------
Sam | Doctor
Joe | Professor
John | Actor
Hailey | Singer
April | Doctor
My goal is to utilize the PIVOT function to display each distinct Occupation as their own column like so:
Doctor | Professor | Singer | Actor
-----------------------------------
Sam | Joe | Hailey | John
April
I went through various stack overflow questions with similar issues, and even utilized the documentation here and followed step by step. My efforts have been futile, so what gives? I am receiving a syntax error every time I run this code. Any suggestions?
Code:
SELECT *
FROM(SELECT Name, Occupation from Occupations) as src
PIVOT
(MAX(Name) FOR Name IN [Doctor], [Professor], [Singer], [Actor]) as piv;
**
- EDIT:
**
I am solving this problem on Hackerrank, and the compiler was MySQL. I changed it to MSSQL and am no longer receiving a syntax error.
Advertisement
Answer
You were missing 2 () One on each side in the PIVOT part, I bolded the brackets I added:
([Doctor], [Professor], [Singer], [Actor]) – These 2
SELECT *
FROM (
SELECT Name, Occupation
FROM Occupations
) as src
PIVOT
(
MAX(Name) FOR Name IN ([Doctor], [Professor], [Singer], [Actor])
) as piv;