How to switch rows and columns in SQL?

Problem

How to switch rows and columns in SQL so that the rows and columns are interchanged?

Given a table named Students with columns Name, Math, and Science representing student names and their scores in Math and Science. We want to transpose this table such that subjects become rows and student names become columns with their respective scores under them.

Input

id	attribute	value
1	Height	175
2	Weight	70
3	Age	30

Try Hands-Om: Fiddle

Create Input Table: Gist

Desired Output

This result represents the switched rows and columns, where the attributes (‘Height’, ‘Weight’, ‘Age’) are now columns, and their corresponding values are in the same row.

Height	Weight	Age
175	70	30

Solution:

    CREATE TEMPORARY TABLE switched_table AS
    SELECT
        MAX(CASE WHEN attribute = 'Height' THEN value END) AS "Height",
        MAX(CASE WHEN attribute = 'Weight' THEN value END) AS "Weight",
        MAX(CASE WHEN attribute = 'Age' THEN value END) AS "Age"
    FROM original_table;

Explanation:

The above query creates a temporary table named switched_table and uses conditional aggregation to switch the rows and columns from the original_table.

It creates columns for each unique value in the attribute column (in this example, ‘Height’, ‘Weight’, and ‘Age’) and pivots the data accordingly. The MAX function is used to aggregate the data for each column.

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 select only rows with max value on a column?
2. How to transpose columns to rows in SQL?
3. How to select first row in each GROUP BY group?
4. How to retrieve the last record in each group in SQL?