Understanding PIVOT operator in SQL Server with Examples

Today, we will quickly see PIVOT operator in SQL Server and why do we need this and its various forms of usages. To understand better, let us think of a transformation of data as below and see how do we approach further.

First off, let us create a CustomerPurchaseTable as below. Please note if you are not heard of DROP IF EXISTS yet, please refer here.

Drop Table if exists  CustomerPurchaseTable

create Table CustomerPurchaseTable( CustomerID int, FruitName varchar(100), Quantity int)
Insert into CustomerPurchaseTable Values
(100,'Apple',10),(100,'Grapes', 5),(101,'Orange',10),(102,'Pineapple',5),(102,'Grapes',10)

Select * From CustomerPurchaseTable

I remember when I started my career I was using SQL Server 7.0 and SQL 2000. It was not so easy task to achieve transformations as of today, particularly, the pivoting had achieved using Select ..CASE statements as below.

Select CustomerID, Sum(Case When FruitName = 'Apple' Then Quantity Else NULL End )'Apple',
				   Sum(Case when FruitName = 'Grapes' Then Quantity Else NULL End ) 'Grapes',
				   Sum(Case when FruitName = 'Orange' Then Quantity Else NULL End ) 'Orange'
From CustomerPurchaseTable
Group by CustomerID

Post SQL Server 2005 version release, there was a new operator introduced “PIVOT” which does the work with ease. I would say, its a fancy form of old way.But, at the same time, I would agree that its a saver at times, we would see that later part of this blog post.

Here is the syntax of PIVOT:

Syntax:
SELECT first_column AS ,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
() AS 
PIVOT 
(
 aggregate_function()
 FOR 
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS ;

Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in ([Apple],[Grapes],[Orange]))as PivotTable

Now, let us look at a case how it would work for a non-fixed type of fruits in the above example? Then, this should be a dynamic transformation that might required. With old approach(prior to SQL Server 2005), this is a complicated one and hard to achieve with Dynamic Cross-Tabs/Pivot Tables. With the introduction of PIVOT that becomes handy. That does not mean that we do not need dynamic query, but it extremely handy to build dynamic list of columns and assign the value to the dynamic Query to produce the transformation. The generation of column list with quotename is much easier than generating the entire sql construct of CASE…WHEN as in the old way.

DECLARE 
    @columns NVARCHAR(MAX) = '', @sql     NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(FruitName) + ','
FROM (Select Distinct FruitName From CustomerPurchaseTable) A
ORDER BY FruitName ASc

SET @columns = LEFT(@columns, LEN(@columns) - 1);
PRINT @columns;

Set @SQL = 'Select * From CustomerPurchaseTable
PIVOT (Sum(Quantity) For FruitName in (' + @columns + '))as PivotTable'

Exec sp_executeSQL @SQL

Iā€™d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s