student as below.
| name | continent | |--------|-----------| | Jack | America | | Pascal | Europe | | Xi | Asia | | Jane | America |Pivot the continent column in this table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia and Europe respectively. It is guaranteed that the student number from America is no less than either Asia or Europe. For the sample input, the output is:
| America | Asia | Europe | |---------|------|--------| | Jack | Xi | Pascal | | Jane | | |Follow-up: If it is unknown which continent has the most students, can you write a query to generate the student report?
join [Accepted]Intuition
\nAssign a separate auto increment row id to each of the continent, and then join them together.
\nAlgorithm
\nTo set the row id for each continent, we need to use session variables.\nFor example, we can use the following statement to assign a auto increment row number for students in America.
\nSELECT \n row_id, America\nFROM\n (SELECT @am:=0) t,\n (SELECT \n @am:=@am + 1 AS row_id, name AS America\n FROM\n student\n WHERE\n continent = \'America\'\n ORDER BY America) AS t2\n;\n
| row_id | America |\n|--------|---------|\n| 1 | Jack |\n| 2 | Jane |\n
Similarly, we can assign other dedicated row id for other continents as the following result.
\n| row_id | Asia |\n|--------|------|\n| 1 | Xi |\n\n| row_id | Europe |\n|--------|--------|\n| 1 | Jesper |\n
Then if we join these 3 temp tables together and using the same row_id as the condition, we can have the following table.
\n| row_id | America | Asia | Europe |\n|--------|---------|------|--------|\n| 1 | Jack | Xi | Pascal |\n| 2 | Jane | | |\n
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
\nSELECT \n America, Asia, Europe\nFROM\n (SELECT @as:=0, @am:=0, @eu:=0) t,\n (SELECT \n @as:=@as + 1 AS asid, name AS Asia\n FROM\n student\n WHERE\n continent = \'Asia\'\n ORDER BY Asia) AS t1\n RIGHT JOIN\n (SELECT \n @am:=@am + 1 AS amid, name AS America\n FROM\n student\n WHERE\n continent = \'America\'\n ORDER BY America) AS t2 ON asid = amid\n LEFT JOIN\n (SELECT \n @eu:=@eu + 1 AS euid, name AS Europe\n FROM\n student\n WHERE\n continent = \'Europe\'\n ORDER BY Europe) AS t3 ON amid = euid\n;\n