MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table occupations as
     select 'Samantha ' as name, 'Doctor' as occupation union all
     select 'Julia    ' as name, 'Actor' as occupation union all
     select 'Maria    ' as name, 'Actor' as occupation union all
     select 'Meera    ' as name, 'Singer' as occupation union all
     select 'Ashely   ' as name, 'Professor' as occupation union all
     select 'Ketty    ' as name, 'Professor' as occupation union all
     select 'Christeen' as name, 'Professor' as occupation union all
     select 'Jane     ' as name, 'Actor' as occupation union all
     select 'Jenny    ' as name, 'Doctor' as occupation union all
     select 'Priya    ' as name, 'Singer' as occupation

Records: 10  Duplicates: 0  Warnings: 0


SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
       MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
       MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
       MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
             ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY occupation) as seqnum
      FROM occupations o
     ) o
GROUP BY seqnum;


Doctor
Professor
Singer
Actor


Samantha
Ashely
Meera
Julia

Jenny
Ketty
Priya
Maria

null
Christeen
null
Jane


SELECT MAX(CASE occupation WHEN 'Doctor' THEN name END) AS Doctor,
       MAX(CASE occupation WHEN 'Professor' THEN name END) AS Professor,
       MAX(CASE occupation WHEN 'Singer' THEN name END) AS Singer,
       MAX(CASE occupation WHEN 'Actor' THEN name END) AS Actor
FROM (SELECT o.*,
             (SELECT COUNT(*)
              FROM occupations o2
              WHERE o2.occupation = o.occupation AND
                    o2.name <= o.name
             ) as seqnum
      FROM occupations o
     ) o
GROUP BY seqnum
ORDER BY seqnum;


Doctor
Professor
Singer
Actor


Jenny
Ashely
Meera
Jane

Samantha
Christeen
Priya
Julia

null
Ketty
null
Maria
Doctor	Professor	Singer	Actor
Samantha	Ashely	Meera	Julia
Jenny	Ketty	Priya	Maria
null	Christeen	null	Jane