Solution


Approach: Using "session variables" and join [Accepted]

Intuition

Assign a separate auto increment row id to each of the continent, and then join them together.

Algorithm

To set the row id for each continent, we need to use session variables. For example, we can use the following statement to assign a auto increment row number for students in America.

SELECT 
    row_id, America
FROM
    (SELECT @am:=0) t,
    (SELECT 
        @am:=@am + 1 AS row_id, name AS America
    FROM
        student
    WHERE
        continent = 'America'
    ORDER BY America) AS t2
;
| row_id | America |
|--------|---------|
| 1      | Jack    |
| 2      | Jane    |

Similarly, we can assign other dedicated row id for other continents as the following result.

| row_id | Asia |
|--------|------|
| 1      | Xi   |

| row_id | Europe |
|--------|--------|
| 1      | Jesper |

Then if we join these 3 temp tables together and using the same row_id as the condition, we can have the following table.

| row_id | America | Asia | Europe |
|--------|---------|------|--------|
| 1      | Jack    | Xi   | Pascal |
| 2      | Jane    |      |        |

One issue you may encounter is the student list for America is not complete if you use regular inner join since there are more records in this list comparing with the other two. So you may have a solution to use the outer join. Correct! But how to arrange the 3 tables? The trick is to put the America list in the middle so that we can use right (outer) join and right (outer) join to connect with other two tables.

MySQL

SELECT 
    America, Asia, Europe
FROM
    (SELECT @as:=0, @am:=0, @eu:=0) t,
    (SELECT 
        @as:=@as + 1 AS asid, name AS Asia
    FROM
        student
    WHERE
        continent = 'Asia'
    ORDER BY Asia) AS t1
        RIGHT JOIN
    (SELECT 
        @am:=@am + 1 AS amid, name AS America
    FROM
        student
    WHERE
        continent = 'America'
    ORDER BY America) AS t2 ON asid = amid
        LEFT JOIN
    (SELECT 
        @eu:=@eu + 1 AS euid, name AS Europe
    FROM
        student
    WHERE
        continent = 'Europe'
    ORDER BY Europe) AS t3 ON amid = euid
;