Problem:
Difficulty: Medium
Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.
Note: Print NULL when there are no more names corresponding to an occupation.
Solution:
SELECT min(Doctor), min(Professor),min(Singer), min(Actor)
FROM(
SELECT ROW_NUMBER() OVER(PARTITION By Doctor,Actor,Singer,Professor order by name asc) AS Rownum,
CASE when Doctor=1 then name else Null end as Doctor,
CASE when Actor=1 then name else Null end as Actor,
CASE when Singer=1 then name else Null end as Singer,
CASE when Professor=1 then name else Null end as Professor
FROM occupations
PIVOT
( COUNT(occupation)
for occupation in(Doctor, Actor, Singer, Professor)) as p
) temp
GROUP BY Rownum ;