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!
First off, let us create a CustomerPurchaseTable as below. Please note if you are not heard of DROP IF EXISTS yet, please refer here.
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.
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:
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.
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
SQLZealots 