What’s on our mind?

Exsilio Solutions is proud to introduce our new blog! Not only is this forum completely integrated with all our social networks, but it is also 100% responsive. Now, you can take Exsilio with you on your phone, tablet, and desktop - redefine what you thought possible!


Understanding the benefits of the PIVOT/UNPIVOT Clause in SQL Server.

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.


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


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.


Eric Ji SQL Blog Pic 1
Eric Ji SQL Blog Pic 2

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.
Eric Ji SQL Blog Pic 3
Eric Ji SQL Blog Pic 4

Comments are closed!

Loading more content...