Here my solution in MySQL

To sum up the step made:

Step 1.

Extract a temporary table contains: name, occupation, rownumber. rownumber counts increasing on each occupation.

Step 2.

Group that table by rownumber

Step 3.

Select each occupation using GROUP_CONCAT.

Explanation

• GROUP_CONCAT() basically returns a string with concanated value from a group. For details MySQL GROUP_CONCAT() function.

• Hence, each sample in an occupation needs to belong to a group, and the incremental id is the same on each group (eg. Doctor: [1, 2, 3, 4], Singer: [1, 2],...).

• Creating group above, I need a temp table which stores name, occupation and an id or rownumber. To get rownumber, I use a window function namely ROW_NUMBER() which will count on each group of occupation. ROW_NUMBER() OVER (ORDER BY name) will create a ID for each row order by name (eg. Abe: 1, Blob: 2,...). Cosidering groups on occupations, I use window function over PARTITION BY OCCULATION.

SELECT 
    GROUP_CONCAT(IF(Occupation="Doctor", Name, NULL) ORDER BY Name) as 'Doctor',
    GROUP_CONCAT(IF(Occupation="Professor", Name, NULL) ORDER BY Name) as 'Professor',
    GROUP_CONCAT(IF(Occupation="Singer", Name, NULL) ORDER BY Name) as 'Singer',
    GROUP_CONCAT(IF(Occupation="Actor", Name, NULL) ORDER BY Name) as 'Actor'
FROM (
    SELECT Name, Occupation, ROW_NUMBER() OVER (PARTITION BY occupation order by name) AS rownumber 
    FROM Occupations
) AS temp
GROUP BY rownumber;