# 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.

Course Preview