Students Report By Geography

Problem

Table: Student

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| name        | varchar |
| continent   | varchar |
+-------------+---------+
This table may contain duplicate rows.
Each row of this table indicates the name of a student and the continent they came from.

Problem Definition

A school has students from Asia, Europe, and America.

Write a solution to pivot the continent column in the Student table so that each name is sorted alphabetically and displayed underneath its corresponding continent. The output headers should be America, Asia, and Europe, respectively.

Example

Output

Try It Yourself

Solution

To pivot the Student table such that each student's name is displayed under their respective continent (America, Asia, Europe) and sorted alphabetically, we can follow a systematic approach. This involves assigning row numbers to each student within their continent based on alphabetical order and then aggregating the names accordingly.

Approach Overview

1. Assign Row Numbers to Students Within Each Continent:

   • For each continent, sort the students' names alphabetically and assign a sequential row number. This helps in aligning students from different continents side by side in the pivoted result.

2. Pivot the Data Based on Row Numbers:

   • Transform the sorted list into a pivoted format where each row corresponds to a specific row number, and each column represents a continent. This aligns students from different continents based on their row numbers.

3. Select and Order the Final Results:

   • Retrieve the pivoted data, ensuring that the continents are displayed as separate columns (America, Asia, Europe). Sort the results based on the row numbers to maintain the alphabetical order.

SQL Query

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 continent,
               NAME,
               Row_number()
                 OVER (
                   partition BY continent
                   ORDER BY NAME) AS rn
        FROM Student)i
GROUP  BY rn 

Step-by-Step Approach

Step 1: Assign Row Numbers to Students Within Each Continent

For each continent (America, Asia, Europe), sort the students' names alphabetically and assign a sequential row number. This row number (rn) will be used to align students from different continents side by side in the pivoted result.

SQL Query:

SELECT continent,
       NAME,
       ROW_NUMBER() OVER (
         PARTITION BY continent
         ORDER BY NAME
       ) AS rn
FROM Student;

Explanation:

• ROW_NUMBER() OVER (PARTITION BY continent ORDER BY NAME) AS rn:
  • Assigns a unique sequential number to each student within their continent based on alphabetical order of their names.
• PARTITION BY continent:
  • Divides the data into partitions for each continent, ensuring row numbers restart for each continent.

Intermediate Output After Step 1:

+-----------+----------+----+
| continent | NAME     | rn |
+-----------+----------+----+
| America   | Jack     | 1  |
| America   | Jane     | 2  |
| Asia      | Xi       | 1  |
| Europe    | Pascal   | 1  |
+-----------+----------+----+

Step 2: Pivot the Data Based on Row Numbers

Transform the sorted list into a pivoted format where each row corresponds to a specific row number (rn), and each column represents a continent. This aligns students from different continents side by side based on their row numbers.

SQL Query:

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 continent,
                NAME,
                ROW_NUMBER() OVER (
                  PARTITION BY continent
                  ORDER BY NAME
                ) AS rn
         FROM Student
       ) i
GROUP  BY rn;

Explanation:

• CASE WHEN continent = 'America' THEN NAME END AS America:
  • Extracts the student's name if they belong to America.
• MAX(...) AS America:
  • Aggregates the names based on the row number (rn). Since there's only one name per continent per rn, MAX effectively selects that name.
• GROUP BY rn:
  • Groups the data by the row number to align students from different continents in the same row.

Intermediate Output After Step 2:

+---------+------+---------+
| America | Asia | Europe  |
+---------+------+---------+
| Jack    | Xi   | Pascal  |
| Jane    | NULL | NULL    |
+---------+------+---------+

Step 3: Select and Order the Final Results

Retrieve the pivoted data with columns America, Asia, and Europe. Each row represents students from different continents aligned based on their alphabetical order. The result should be ordered by rn to maintain the sequence.

Final SQL Query:

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 continent,
                NAME,
                ROW_NUMBER() OVER (
                  PARTITION BY continent
                  ORDER BY NAME
                ) AS rn
         FROM Student
       ) i
GROUP  BY rn 
ORDER  BY rn ASC;

Explanation:

• ORDER BY rn ASC:
  • Ensures that the rows are ordered based on the row numbers, maintaining the alphabetical sequence of student names within each continent.

Final Output:

+---------+------+---------+
| America | Asia | Europe  |
+---------+------+---------+
| Jack    | Xi   | Pascal  |
| Jane    | NULL | NULL    |
+---------+------+---------+