How to switch rows and columns in SQL so that the rows and columns are interchanged?
Given a table named
Students with columns
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.
Try Hands-Om: Fiddle
Create Input Table: Gist
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.
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;
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.