MS SQL SERVER

WITH ordered AS ( SELECT Name, Occupation, ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS rn FROM OCCUPATIONS ), ctc as ( SELECT * FROM ( SELECT Name, Occupation, rn FROM ordered ) AS src PIVOT ( MAX(Name) FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor]) ) AS p ) SELECT Doctor, Professor, Singer, Actor FROM ctc ORDER BY rn;

MS SQL Server

WITH CTE_Enumerada AS (
    SELECT
        Name,
        Occupation,
        ROW_NUMBER() OVER (PARTITION BY Occupation ORDER BY Name) AS rn
    FROM OCCUPATIONS
), PivotFinal AS (
    SELECT *
    FROM CTE_Enumerada
    PIVOT (
        MAX(Name)
        FOR Occupation IN ([Doctor], [Professor], [Singer], [Actor])
    ) AS p
)
SELECT Doctor, Professor, Singer, Actor FROM PivotFinal
ORDER BY rn;

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 name, occupation,
           ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) AS number
    FROM OCCUPATIONS
) AS sub
GROUP BY number;

Isso funcionou!!

SELECT MAX(IF(Occupation = 'Doctor', Name, NULL)) AS Doctor, MAX(IF(Occupation = 'Professor', Name, NULL)) AS Professor, MAX(IF(Occupation = 'Singer', Name, NULL)) AS Singer, MAX(IF(Occupation = 'Actor', Name, NULL)) AS Actor FROM ( SELECT @doc := IF(Occupation = 'Doctor', @doc + 1, @doc) AS rn_doc, @prof := IF(Occupation = 'Professor', @prof + 1, @prof) AS rn_prof, @sing := IF(Occupation = 'Singer', @sing + 1, @sing) AS rn_sing, @act := IF(Occupation = 'Actor', @act + 1, @act) AS rn_act, Name, Occupation, CASE Occupation WHEN 'Doctor' THEN @doc WHEN 'Professor' THEN @prof WHEN 'Singer' THEN @sing WHEN 'Actor' THEN @act END AS rn FROM ( SELECT Name, Occupation FROM OCCUPATIONS ORDER BY Occupation, Name ) AS sorted, (SELECT @doc := 0, @prof := 0, @sing := 0, @act := 0) AS vars ) AS numbered Group By rn Order By rn; ) AS numbered GROUP BY rn ORDER BY rn;

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 ranked GROUP BY rn ORDER BY rn;