SELECT 
    [Doctor],
    [Professor],
    [Singer],
    [Actor]
FROM (
    SELECT 
        [Name],
        [Occupation],
        ROW_NUMBER() OVER(
            PARTITION BY [Occupation] ORDER BY [Name]
        ) AS rowNumber
    FROM OCCUPATIONS
) AS SourceTable
PIVOT (
    MAX([NAME])
    FOR [Occupation] IN (
        Doctor,
        Professor,
        Singer,
        Actor
    )
) AS PivotTable;