HackerRank 'Occupations' MySQL

PROBLEM
Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns (Doctor, Professor, Singer, and Actor) in that specific order, with their respective names listed alphabetically under each column.

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.

EXPLANATION
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

SOLUTION

EXPLANATION
The data is initially organized vertically, and the exercise requires the data to be arranged in a horizontal format. That is, we need to pivot the Occupation column from the OCCUPATIONS table so that each Name is sorted alphabetically and displayed under its respective occupation, with a separate column for each occupation.

The output should contain four columns (Doctor, Professor, Singer, and Actor) with the names listed in alphabetical order under each column and should return NULL when there are no more names corresponding to an occupation.
MySQL does not have a built-in PIVOT function. To solve this problem, it will be necessary to create a pivot table using CASE(), ROW_NUMBER() and MAX() to reorganize the data from the OCCUPATIONS table.

SELECT 
    MAX(
      CASE 
        WHEN occupation = 'Doctor' 
        THEN name END
    ) AS Doctor,
    MAX(
      CASE 
        WHEN occupation = 'Professor' 
        THEN name END
    ) AS Professor,
    MAX(
      CASE 
        WHEN occupation = 'Singer' 
        THEN name END
    ) AS Singer,
    MAX(
      CASE 
        WHEN occupation = 'Actor' 
        THEN name END
    ) AS Actor;

The CASE expression organizes each name into the correct column in the pivot table. CASE checks each row and correctly distributes the names among the corresponding columns. The MAX() function selects the non-null name in each group.
What happens is that if the row analyzed by the CASE logic is 'Doctor', the name is placed in the 'Doctor' column; otherwise, it returns NULL. The same applies to 'Professor', 'Singer', and 'Actor'.

FROM (
   SELECT
       name,
       occupation,
       ROW_NUMBER() OVER(
         PARTITION BY occupation ORDER BY name
      ) AS occ_rownumber
   FROM OCCUPATIONS
) AS subquery;

The subquery uses ROW_NUMBER() to create a unique index for each name within its occupation, allowing the data to be aligned within a partition by numbering the rows by occupation.

By partitioning by Occupation and ordering by Name, we ensure that each occupation has its entries numbered in alphabetical order. This returns a list where each name is assigned a row number within its occupation.

The ROW_NUMBER logic allows transforming the vertical list into a table, where each column represents an occupation, and each row contains a name sorted alphabetically.

This way, we can correctly pivot the data in MySQL.