0% completed
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
-
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.
-
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.
-
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.
- Retrieve the pivoted data, ensuring that the continents are displayed as separate columns (
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
.
- Extracts the student's name if they belong to
MAX(...) AS America
:- Aggregates the names based on the row number (
rn
). Since there's only one name per continent perrn
,MAX
effectively selects that name.
- Aggregates the names based on the row number (
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 | +---------+------+---------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible