EASY STEPS :

WITH order_name AS( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rank FROM OCCUPATIONS ) SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name ELSE NULL END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name ELSE NULL END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name ELSE NULL END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name ELSE NULL END) AS Actor FROM order_name GROUP BY rank ORDER BY rank;

with tabel as ( select name, occupation, row_number() over (partition by occupation order by name asc) as angka from occupations ), tabeldokter as ( select name as doctor_name, angka from tabel where occupation = 'Doctor' ), tabelprofessor as ( select name as professor_name, angka from tabel where occupation = 'Professor' ), tabelsinger as ( select name as singer_name, angka from tabel where occupation = 'Singer' ), tabelactor as ( select name as actor_name, angka from tabel where occupation = 'Actor' ) -- Emulating FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN with UNION select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabeldokter left join tabelprofessor on tabeldokter.angka = tabelprofessor.angka left join tabelsinger on tabeldokter.angka = tabelsinger.angka left join tabelactor on tabeldokter.angka = tabelactor.angka

union

select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelprofessor left join tabeldokter on tabeldokter.angka = tabelprofessor.angka left join tabelsinger on tabelprofessor.angka = tabelsinger.angka left join tabelactor on tabelprofessor.angka = tabelactor.angka

union

select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelsinger left join tabeldokter on tabelsinger.angka = tabeldokter.angka left join tabelprofessor on tabelsinger.angka = tabelprofessor.angka left join tabelactor on tabelsinger.angka = tabelactor.angka

union

select tabeldokter.doctor_name, tabelprofessor.professor_name, tabelsinger.singer_name, tabelactor.actor_name from tabelactor left join tabeldokter on tabelactor.angka = tabeldokter.angka left join tabelprofessor on tabelactor.angka = tabelprofessor.angka left join tabelsinger on tabelactor.angka = tabelsinger.angka;

• MAX is used to pick the name in a grouped row.
• ROW_NUMBER(): Assigns a unique number to each row.
• PARTITION BY Occupation: Resets the row number for each occupation group.
• ORDER BY Name: Sorts names alphabetically within each occupation group.

SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Doctor, MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor, MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer, MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor FROM( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY NAME)AS RN FROM OCCUPATIONS ) AS SubQuery GROUP BY RN ORDER BY RN

select Doctor, Professor, Singer, Actor from ( select Name, Occupation, row_number() over(partition by occupation order by Name) row_num from OCCUPATIONS ) base_table pivot ( max(Name) for Occupation in ('Doctor','Professor', 'Singer', 'Actor') ) order by row_num

MySQL

SELECT d.name, p.name, s.name, a.name
FROM (SELECT name, ROW_NUMBER() OVER (ORDER BY name ASC) AS id
      FROM OCCUPATIONS
      WHERE Occupation = "Doctor") AS d
RIGHT JOIN (SELECT name, ROW_NUMBER() OVER (ORDER BY name ASC) AS id
      FROM OCCUPATIONS
      WHERE Occupation = "Professor") AS p ON d.id = p.id
LEFT JOIN (SELECT name, ROW_NUMBER() OVER (ORDER BY name ASC) AS id
      FROM OCCUPATIONS
      WHERE Occupation = "Singer") AS s ON p.id = s.id
LEFT JOIN (SELECT name, ROW_NUMBER() OVER (ORDER BY name ASC) AS id
      FROM OCCUPATIONS
      WHERE Occupation = "Actor") AS a ON p.id = a.id