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.
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
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:
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
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 ;
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.
Select * From CustomerPurchaseTable PIVOT (Sum(Quantity) For FruitName in ([Apple],[Grapes],[Orange]))as PivotTable
I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!
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