IN MYSQL

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 Occupation, Name, ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name ASC) AS RowNum FROM OCCUPATIONS ) AS occ_rank GROUP BY RowNum ORDER BY RowNum;

WITH Doctor AS (select Name as Doctor,row_number() over (order by Name) as row_num from occupations where Occupation='Doctor'), Professor AS (select Name as Professor,row_number() over (order by Name) as row_num from occupations where Occupation='Professor'), Singer AS (select Name as Singer,row_number() over (order by Name) as row_num from occupations where Occupation='Singer'), Actor AS (select Name as Actor,row_number() over (order by Name) as row_num from occupations where Occupation='Actor')

select Doctor,Professor,Singer,Actor from Doctor full join Professor on Professor.row_num=Doctor.row_num full join Singer on Singer.row_num=Professor.row_num full join Actor on Actor.row_num=Singer.row_num

WITH ranked AS ( SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ) 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 ranked GROUP BY rn ORDER BY rn;

-- MS SQL Server
WITH occ_rank AS (
SELECT
Occupation,
Name,
ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name ASC) AS RowNum
FROM
OCCUPATIONS
)
SELECT
Doctor,
Professor,
Singer,
Actor
FROM
occ_rank
PIVOT
(
MAX(Name)
FOR Occupation IN (
[Doctor], [Professor], [Singer], [Actor]
)
) AS Pivot_table
ORDER BY
Pivot_table.RowNum;

with maxtable as ( select name, row_number() over (order by name) as row_num from occupations where occupation = (select occupation from ( select occupation,count(name) as nos from occupations group by occupation) as counts order by nos desc limit 1) ), Doctors as ( select name, row_number() over (order by name) as row_num from occupations where occupation = 'Doctor' ), Professors as ( select name, row_number() over (order by name) as row_num from occupations where occupation = 'Professor' ), Singers as ( select name , row_number() over (order by name) as row_num from occupations where occupation = 'Singer' ), Actors as ( select name , row_number() over (order by name) as row_num from occupations where occupation = 'Actor' )

select D.name, P.name, S.name, A.name from maxtable M LEFT JOIN Doctors D on M.row_num = D.row_num LEFT JOIN Professors P on M.row_num = P.row_num LEFT JOIN Singers S on M.row_num = S.row_num LEFT JOIN Actors A on M.row_num = A.row_num