Transforming data is a key part of what we do as data analytics consultants. Tools such as Tableau Prep, Alteryx and Power Query help us to pivot data. Here is a guide of how to pivot data in SQL.
Unpivoting - Columns to Rows
If we look at the diagram below, on the left hand side we have area A showing where all the values within our table would be, and area B is showing the headers of our data set. Unpivoting the data would result in it being structured as shown on the right hand side of the image. The example green area would include the number of tennis games won in each set.
In order to transform our data from the format on the left to the format on the right we use UNPIVOT function in SQL to do we use the syntax below. In this example Games_Won is the name of the column that we have decided.
Pivoting - Rows To Columns
Looking at the table on the left of the diagram below. We can see that there is a column that has sales and profit in. This is the data we want, however it is not in the format that we want. We would like to have sales and profit as headers. As shown on the right hand side.
To do this transformation in SQL we will need the following syntax:
Just like that you know have the ability to Pivot and Unpivot your data using SQL!