SQL Transpose – rows to columns
In SQL, there are several ways to transpose data from rows to columns, also known as “horizontal to vertical” or “flipping” the data. One common way is to use the PIVOT
function.
The PIVOT
function allows you to rotate rows into columns, effectively transposing the data.
The basic syntax for using the PIVOT
function is as follows:
SELECT ..., [column_to_become_new_column_1], [column_to_become_new_column_2], ...FROM ... PIVOT (aggregate_function(column_to_aggregate) FOR column_to_become_new_columns IN ([list_of_values_to_become_new_columns]))
In this syntax, ...
represents any additional columns that you want to include in the output, aggregate_function
is the function used to aggregate the values in the column that you want to transpose, column_to_aggregate
is the name of the column that you want to transpose, column_to_become_new_columns...