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:

[crayon-5c4617791bbfc279112198/]

Parameters or Arguments

first_column

A column or expression that will display as the first column in the pivot table.

first_column_alias

The column heading for the first column in the pivot table.

pivot_value1, pivot_value2, ... pivot_value_n

A list of values to pivot.

source_table

A SELECT statement that provides the source data for the pivot table.

source_table_alias

An alias for source_table.

aggregate_function

An aggregate function such as SUM, COUNT, MIN, MAX, or AVG.

aggregate_column

The column or expression that will be used with the aggregate_function.

pivot_column

The column that contains the pivot values.

pivot_table_alias

An alias for the pivot table.

Example

[crayon-5c4617791bc1d013102874/]

[crayon-5c4617791bc30587913895/]

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.
[crayon-5c4617791bc43350444124/]

[crayon-5c4617791bc53533848169/]