SQL Cross Tab – columns to rows
A cross tab
or crosstab
query is a type of query that creates a matrix-like output with the values of one column on the x axis and the values of another column on the y axis. It is also known as a pivot
or transpose
query. The goal of data wrangling is to transform raw data into a format that is useful for analysis, and a cross tab query is one way to do that.
Here’s an example of how you might use a crosstab
query to transform the data in the delivery_data
table so that the rows and columns are rearranged in a way that is more meaningful for your analysis:
delivery_id |
delivery_date |
route |
delivery_status |
vehicle_type |
1 |
1/1/2021 |
Route A |
Delivered |
truck |