The PIVOT Clause
The SQL Server PIVOT clause allows you to write a cross-tabulation which means that you can aggregate your results and rotate rows into columns. UNPIVOT clause is to do the reverse procedure to rotate the columns into rows. Pivot query help us to generate an interactive table that quickly combines and compares large amounts of data. We can rotate its rows and columns to see different summaries of the source data, and we can display the details for areas of interest at a glance.
In this blog, I will start with the base case where the Pivot Columns are given and followed by the Pivot Columns are not pre-determined beforehand.
Here's a link to the pivot SQL code file I will be referencing throughout this blog: Pivot SQL Eric Ji
Base Case - Fixed Pivot Columns
The base case is that the pivot columns are fixed. In this case, you can follow the following syntax to get the table pivoted.
- The syntax for the fixed pivot columns case is as below:
Parameters or Arguments
A column or expression that will display as the first column in the pivot table.
The column heading for the first column in the pivot table.
pivot_value1, pivot_value2, ... pivot_value_n
A list of values to pivot.
A SELECT statement that provides the source data for the pivot table.
An alias for source_table.
The column or expression that will be used with the aggregate_function.
The column that contains the pivot values.
An alias for the pivot table.
Advanced Case - Dynamic Pivot Columns
In the above example, we can tell that the Pivot is fairly simple if the Pivot Columns (VEG, SODA, MILK, BEER, CHIPS) are given. However, there are some cases that the pivot columns are not given beforehand. In this case, we need to run a query first to get the pivot columns and prepare pivot query by combining the dynamic Sql and pivot clause together. The example is given below.