SQL – Pivot - Unpivot, Dynamic Pivot Concepts
In this article, we will understand the PIVOT and UnPivot Concept in SQL Server. We will discuss the scenario when we need to understand and use Dynamic PIVOT Concept in SQL.
PIVOT concept is used to summerize the data using SQL Query.
Lets understand the concept in brief.
Pivot Concept
The PIVOT statement is used for changing rows into columns in a SQL Query. It provides an easy mechanism in SQL Server to transform rows into columns. PIVOT Concept is also used to summarize the data.
The syntax for PIVOT Statement is written as:
SELECT columns
FROM table
PIVOT
(
Aggregate Function(Measure Column)
FOR Pivot Column IN ([Pivot Column Values])
)
AS Alias
Lets understand the pivot concept with example.
Create a sample table using following query.
CREATE TABLE Sample_Pivot
(DealerName VARCHAR(50), CarName VARCHAR(30), QTY INT)
GO
Insert the data into the "Sample_Pivot" table
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Robin Motors','Audi',10)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Robin Motors','Mercedese-Benz',6)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Car World','Audi',12)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Robin Motors','Honda',12)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Car World','Honda',5)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Car World','Mercedese-Benz',24)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Harry Motors','Audi',15)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Harry Motors','Mercedese-Benz',10)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Harry Motors','Honda',15)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Harry Motors','BMW',5)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Car World','BMW',7)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Robin Motors','BMW',10)
Select and check entries in the "Sample_Pivot" table
SELECT *
FROM Sample_Pivot
Use below query to pivot the table data order by CarName column.
SELECT *
FROM (
SELECT DealerName, CarName, QTY
FROM Sample_Pivot) spu
PIVOT (SUM(QTY) FOR DealerName IN ([Robin Motors],[Car World],[Harry Motors])) AS pvt
ORDER BY CarName
Use below query to pivot the table data order by DealerName column.
SELECT *
FROM (
SELECT DealerName, CarName, QTY
FROM Sample_Pivot) spu
PIVOT (SUM(QTY) FOR CarName IN (Audi, Honda,[Mercedese-Benz],BMW)) AS pvt
ORDER BY DealerName
Unpivot Concept
UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows.
SELECT DealerName, CarName, QTY
FROM
(
SELECT *
FROM (
SELECT DealerName, CarName, QTY
FROM Sample_Pivot) up
PIVOT
(SUM(QTY) FOR CarName IN (Audi,Honda,[Mercedese-Benz],BMW)) AS pvt) p
UNPIVOT
(QTY FOR CarName IN (Audi,Honda,[Mercedese-Benz],BMW)
) AS Unpvt
In above query, first it will PIVOT the data and then it will do the UnPivot operation to get the original data.
Here, We are getting original data back while doing Unpivot operation.
However, It is not possible in all scenarios. It is not compulsory that we will get the original data back after doing unpivot operation.
Conclusion
From the above example it is clear that UNPIVOT is the reversal of the PIVOT operation. However, it is not the exact reversal of PIVOT operation as PIVOT operation generates the aggregated result.
Hence, UNPIVOT will not be able to split the aggregated result back to the original rows as they were present prior to the PIVOT operation.
Need of Dynamic PIVOT Concept
PIVOT relational operator can be used to transform columns distinct values as Columns in the result set
by mentioning all the distinct column values in the PIVOT operators PIVOT columns IN clause.
SELECT *
FROM (
SELECT DealerName, CarName, QTY
FROM Sample_Pivot) spu
PIVOT (SUM(QTY) FOR CarName IN (Audi, Honda,[Mercedese-Benz],BMW)) AS pvt
ORDER BY DealerName
This type of PIVOT query is called Static PIVOT query, because if the PIVOT column in the source table gets extra unique values after the initial query then that will not reflect in the PIVOT query result unless it is mentioned in the PIVOT Columns IN clause. Static PIVOT queries are fine as long as we know that the PIVOT column values never change.
When we do not know the distinct PIVOT column values then Dynamic pivot concept is used.
Dynamic Pivot concept
Dynamic Pivot concept is used when we do not know the distinct values of pivot columns.
In dynamic PIVOT, we don't need to mention the PIVOT columns each unique values and no need to worry if PIVOT column gets extra unique values after the initial query.
Refer the following query to understand the dynamic pivot concept
Here, we do not have to give the distinct pivot column values. Using dynamic query, we get the distinct values for pivot columns.
If some records are added then using dynamic pivot concept, it will reflect in result unlike static pivot concept.
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Robin Motors','Maruti',18)
INSERT INTO Sample_Pivot(DealerName, CarName, QTY)
VALUES('Car World','Maruti',5)
Static PIVOT Query
SELECT *
FROM (
SELECT DealerName, CarName, QTY
FROM Sample_Pivot) spu
PIVOT (SUM(QTY) FOR CarName IN (Audi, Honda,[Mercedese-Benz],BMW)) AS pvt
ORDER BY DealerName
In above query result, even if we have inserted two new records but it will not appear in the result set because the value for “Maruti" is not mentioned in pivot columns clause.
Dynamic PIVOT Query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @CarName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @CarName= ISNULL(@CarName + ',','')
+ QUOTENAME(CarName)
FROM (SELECT DISTINCT CarName FROM Sample_Pivot) AS CarName
--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
N'SELECT DealerName, ' + @CarName + '
FROM Sample_Pivot
PIVOT(SUM(QTY)
FOR CarName IN (' + @CarName + ')) AS PVT'
--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery
Hence, When we do not know the distinct column values for pivot columns, dynamic pivot concept is useful.
Points to remember
• Aggregate function is must while using PIVOT and UnPivot concept.
• UNPIVOT is not the exact reverse of PIVOT.
• PIVOT performs an aggregation and therefore, merges possible multiple rows into a single row in the output.
• UNPIVOT does not reproduce the original table-valued expression result because rows have been merged.
• We can get the original table back if aggregate functions was not applied on the data or data was in such form that aggregate function might have not made any difference