Students Report By Geography

hard database sql

Problem

Student(name, continent). Pivot into three columns America / Asia / Europe, each sorted lexicographically and padded with NULL.

Inputs=[('Jane','America'),('Pascal','Europe'),('Xi','Asia'),('Jack','America')]

Output[('Jack','Xi','Pascal'),('Jane',null,null)]

Each continent has its names sorted; shorter continents pad with NULL.

rows (compact)

SELECT MAX(CASE WHEN continent='America' THEN name END) AS America,
       MAX(CASE WHEN continent='Asia' THEN name END) AS Asia,
       MAX(CASE WHEN continent='Europe' THEN name END) AS Europe
FROM (SELECT name, continent, ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name) rn FROM Student) t
GROUP BY rn;

Explanation

We need to turn one tall list of (name, continent) rows into three side-by-side columns, with each continent's names sorted and shorter lists padded with NULL. The trick is to give each name a row number within its continent and then group by that number.

The inner query uses ROW_NUMBER() OVER (PARTITION BY continent ORDER BY name). This restarts the count for each continent and orders names alphabetically, so the 1st America name and the 1st Asia name both get rn = 1.

The outer query does GROUP BY rn, putting all the "1st place" names on one output row, all the "2nd place" names on the next, and so on. Inside each group, MAX(CASE WHEN continent = ... THEN name END) pulls out the one name belonging to each continent (and yields NULL when that continent has no entry at that rank).

Example: America sorts to [Jack, Jane], Asia is [Xi], Europe is [Pascal]. Row rn = 1 becomes (Jack, Xi, Pascal); row rn = 2 only has America's Jane, so it becomes (Jane, null, null).

Time: O(n) Space: O(n)