How to efficiently convert rows to columns in SQL?
Learn how to efficiently convert rows to columns in SQL using PIVOT and CASE statements. Includes example problem, input table, and working solutions.
Problem
Given a table, how to efficiently convert rows to columns?
So that, the records in a given row become column names and the values of the respective columns are taken from another column from the original table?
Input
| employee_id | skill_name | skill_level |
|---|---|---|
| 1 | Java | 4 |
| 1 | SQL | 3 |
| 1 | Python | 5 |
| 2 | Java | 3 |
| 2 | SQL | 4 |
| 3 | Python | 4 |
| 3 | JavaScript | 3 |
Try Hands-On: Fiddle
Create Input Table: Gist
Desired Output
| employee_id | Java | Sequel | Python | JavaScript |
|---|---|---|---|---|
| 1 | 4 | 3 | 5 | |
| 2 | 3 | 4 | ||
| 3 | 4 | 3 |
Solution 1:
To efficiently convert rows to columns, we can use MySQL’s CASE statement along with the GROUP BY clause.
In this case, we’ll pivot the skill_name column into separate columns for each skill and display the corresponding skill level.
Using CASE WHEN
SELECT
employee_id,
MAX(CASE WHEN skill_name = 'Java' THEN skill_level END) AS Java,
MAX(CASE WHEN skill_name = 'SQL' THEN skill_level END) AS Sequel,
MAX(CASE WHEN skill_name = 'Python' THEN skill_level END) AS Python,
MAX(CASE WHEN skill_name = 'JavaScript' THEN skill_level END) AS JavaScript
FROM employee_skills
GROUP BY employee_id;Explanation:
We use the CASE statement to conditionally select the skill level for each skill name.
The GROUP BY clause groups the results by employee_id, ensuring that each employee gets a single row in the output.
We use MAX() to aggregate the skill levels for each skill. This is necessary because there may be multiple rows for the same skill for an employee, and we want to select the highest skill level.