Approach: Using "session variables" and
Assign a separate auto increment row id to each of the continent, and then join them together.
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.
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 ;