mySQL pivoting a table . Removing NULL values

Question

So I have to pivot a table and for all the missing values to replace with a null. The problem starts when I am trying to sort the new columns. All the null values come at the beginning, is there a way for me to trim somehow the total length of a column or exclude Nulls until the first name is reached?

Here is the 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.

Table overview : https://i.sstatic.net/48zB1.png

what i tried so far :

SELECT  
    CASE WHEN occupation = 'Doctor' THEN name  END AS col1,
    CASE WHEN occupation = 'Professor' THEN name END as col2,
    CASE WHEN occupation = 'Singer' THEN name END as col3,
    CASE WHEN occupation = 'Actor' THEN name END as col4
    FROM OCCUPATIONS
    GROUP BY occupation,name
    order by col1,col2,col3,col4 ASC;

what i get back:

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

example of what i want to get back:

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

Answer 1

assign a row number and group by it

drop table if exists t;

create table t(name varchar(1), occupation varchar(2));
insert into t values
('b','dr'),('a','dr'),('c','dr'),
('z','ok'),('a','ok');


with cte as
(select name, occupation, 
         row_number() over (partition by occupation order by name) rn
from t)
select   rn,
            max(case when occupation = 'dr' then name else null end) 'dr',
            max(case when occupation = 'ok' then name else null end) 'ok'
from cte
group by rn;

+----+------+------+
| rn | dr   | ok   |
+----+------+------+
|  1 | a    | a    |
|  2 | b    | z    |
|  3 | c    | NULL |
+----+------+------+
3 rows in set (0.023 sec)