Pivot table in alphabetical order

Am new to MySQL, I want to pivot a table
Table contains two columns only Name and Occupation Where no null values are inserted

I want to pivot the occupation column so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor
I tried this query, Where am able to get them to the point where the column is pivoted

SELECT ( CASE 
           WHEN occupation = 'Doctor' THEN NAME 
           ELSE NULL 
         END ) AS 'Doctor', 
       ( CASE 
           WHEN occupation = 'Professor' THEN NAME 
           ELSE NULL 
         END ) AS 'Professor', 
       ( CASE 
           WHEN occupation = 'Singer' THEN NAME 
           ELSE NULL 
         END ) AS 'Singer', 
       ( CASE 
           WHEN occupation = 'Actor' THEN NAME 
           ELSE NULL 
         END ) AS 'Actor' 
FROM   occupations 
ORDER  BY NAME;

My output as:

Aamina NULL NULL NULL
NULL Ashley NULL NULL
NULL Belvet NULL NULL
NULL Britney NULL NULL
NULL NULL Christeen NULL
NULL NULL NULL Eve
NULL NULL Jane NULL
NULL NULL NULL Jennifer
NULL NULL Jenny NULL
Julia NULL NULL NULL
NULL NULL NULL Ketty
NULL NULL Kristeen NULL
NULL Maria NULL NULL
NULL Meera NULL NULL
NULL Naomi NULL NULL
Priya NULL NULL NULL
NULL Priyanka NULL NULL
NULL NULL NULL Samantha

I can’t figure a way out how can I can get output like:

Aamina Ashley Christeen Eve
Julia Belvet Jane Jennifer
Priya Britney Jenny Ketty
NULL Maria Kristeen Samantha
NULL Meera NULL NULL
NULL Naomi NULL NULL
NULL Priyanka NULL NULL

If someone could explain it to me, It would be really helpful. Thanks

Answer

You can do this with window functions (available in MySQL 8.0) and aggregation:

select 
    max(case when occupation = 'Doctor'    then name end) doctor,
    max(case when occupation = 'Professor' then name end) professor,
    max(case when occupation = 'Singer'    then name end) singer,
    max(case when occupation = 'Actor'     then name end) actor
from (
    select o.*,
        row_number() over(partition by occupation order by name) rn
    from occupations o
) o
group by rn

Advertisement

Answer