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;

​x
 
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

 
Aamina NULL NULL NULLNULL Ashley NULL NULLNULL Belvet NULL NULLNULL Britney NULL NULLNULL NULL Christeen NULLNULL NULL NULL EveNULL NULL Jane NULLNULL NULL NULL JenniferNULL NULL Jenny NULLJulia NULL NULL NULLNULL NULL NULL KettyNULL NULL Kristeen NULLNULL Maria NULL NULLNULL Meera NULL NULLNULL Naomi NULL NULLPriya NULL NULL NULLNULL Priyanka NULL NULLNULL 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

 
Aamina Ashley Christeen EveJulia Belvet Jane JenniferPriya Britney Jenny KettyNULL Maria Kristeen SamanthaNULL Meera NULL NULLNULL Naomi NULL NULLNULL 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

 
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) actorfrom (    select o.*,        row_number() over(partition by occupation order by name) rn    from occupations o) ogroup by rn​

Advertisement

Answer