with main_cte as
(select *,
case when occupation = 'Doctor' then name end as 'Doctor_column',
case when occupation = 'Professor' then name end as 'Professor_column',
case when occupation = 'Singer' then name end as 'Singer_column',
case when occupation = 'Actor' then name end as 'Actor_column',
row_number() over(partition by occupation order by Name) as 'id'
from occupations),
doc_cte as
(select id, Doctor_column from main_cte 
where occupation = 'Doctor'),
prof_cte as
(select id, Professor_column from main_cte 
where occupation = 'Professor'),
sing_cte as
(select id, Singer_column from main_cte 
where occupation = 'Singer'),
act_cte as
(select id, Actor_column from main_cte 
where occupation = 'Actor')
select 
doc.Doctor_column,
prof.Professor_column, 
sing.Singer_column,
act.Actor_column
from 
prof_cte prof left join sing_cte sing
on prof.id = sing.id
left join act_cte act
on prof.id = act.id
left join doc_cte doc
on prof.id = doc.id;