Syntax Error pivoting a SQL table using string columns

I have a table Occupation like such:

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.

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;

Advertisement

Answer