Pivot Occupation Column in OCCUPATIONS Table on HackerRank SQL
Problem
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.
Input Format
The OCCUPATIONS table is described as follows:
Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.
Sample Input
Sample Output
Jenny Ashley Meera Jane
Samantha Christeen Priya Julia
NULL Ketty NULL Maria
Solution:
SELECT
MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor,
MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor,
MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer,
MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor
FROM (
SELECT Name, Occupation,
ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS RowNum
FROM OCCUPATIONS
) AS SortedOccupations
GROUP BY RowNum
ORDER BY RowNum;
Explanation:
- Sorting Names: The ROW_NUMBER function is used to assign a unique row number to each name within its occupation category, ordered alphabetically.
- Pivoting Data: We then use conditional aggregation to pivot the data, selecting names into columns based on their occupation and using the assigned row number to group the rows together.
- Handling NULLs: If there are fewer names for an occupation, MAX with conditional aggregation ensures that NULLs are filled where there are no corresponding names.
