How to efficiently convert rows to columns in SQL?

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

Try Hands-On: Fiddle

Create Input Table: Gist

Desired Output

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.

Recommended Courses

1. SQL for Data Science – Level 1
2. SQL for Data Science – Level 2
3. SQL for Data Science – Level 3

Recommended Tutorial

1. Introduction to SQL
2. SQL Window Functons – Made Simple and Easy
3. SQL Subquery

More SQL Questions

1. How to split values to multiple rows in SQL?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?
4. How to get the rows which have the max value for a column for each group in another column