It is well known that
PIVOT is one of the new features included in SQL Server 2005 which allows to convert rows into columns. But what happens if we wanted to make it dynamic going beyond limits respect to the number of columns? Today in this post I am going to show you how to do it (applies to SQL Server 2005 through SQL Server 2017). To begin with, we are going to use the following example to analyse some data, which will be pivoted shortly after.
USE AdventureWorks
GO
SELECT CustomerID,YEAR(DueDate) [Year], TotalDue
FROM Sales.SalesOrderHeader
ORDER BY CustomerID
According to the result set, there are many sales per customer between the years 2001 and 2004.
In order to pivot the 'TotalDue' per 'Year' we do need to indicate each year in the PIVOT clause. In this case we do also need to know the years which will be taken into account. For instance, this query will pivot 'TotalDue' for the years 2001, 2002, 2003, and 2004.
SELECT CustomerID, [2001] AS '2001', [2002] AS '2002', [2003] AS '2003', [2004] AS '2004'
FROM (
SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
) pvt
PIVOT (SUM(TotalDue) FOR [Year] IN ([2001],[2002],[2003],[2004])) AS Child
ORDER BY CustomerID
Having successfully executed the query, we will get the following nice result:
Up to now, everything seems to be perfect. Nevertheless, what's going on if we wanted to pivot for many more years? Obviously, we would have to deal with a big limitation at first glance since we will need to add the years manually inside the query, but it does not make sense for a real business case. As a result, we can say that PIVOT is not scalable, I mean that PIVOT is not 'dynamic' by design. Luckily, the purpose of this post is to show how to implement an algorithm to simulate a dynamic PIVOT in SQL Server by using the native PIVOT clause and
sp_executesql.
The algorithm is quite simple and does not deserve major explanation, however, I am going to say that this will only create a query dynamically to pivot 'TotalDue' by adding all years inside, and finally the output code will be executed via sp_executesql.
DECLARE @TableYears AS TABLE([Year] INT NOT NULL)
DECLARE @Year INT, @YearsPVT NVARCHAR(MAX)
INSERT INTO @TableYears SELECT DISTINCT YEAR(DueDate) AS [Year] FROM Sales.SalesOrderHeader
SET @Year = (SELECT MIN([Year]) FROM @TableYears)
SET @YearsPVT=N''
WHILE @Year IS NOT NULL
BEGIN
SET @YearsPVT = @YearsPVT + N',['+ CONVERT(NVARCHAR(10),@Year) + N']'
SET @Year = (SELECT MIN([Year]) FROM @TableYears WHERE [Year]>@Year)
END
SET @YearsPVT = SUBSTRING(@YearsPVT,2,LEN(@YearsPVT))
PRINT @YearsPVT
DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'SELECT *
FROM (
SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader
) pvt
PIVOT (SUM(TotalDue) FOR [Year] IN (' + @YearsPVT + ')) AS Child
ORDER by CustomerID'
EXECUTE sp_executesql @SQL
Now I am going to illustrate another example by using data from Northwind database.
SELECT P.ProductID, C.CategoryName, OD.UnitPrice * OD.Quantity AS TotalAmount
FROM Products P
INNER JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
INNER JOIN Categories C
ON C.CategoryID=P.CategoryID
Here the simple result without PIVOT.
Using PIVOT in its old-fashioned way:
SELECT ProductID, [Beverages], [Condiments], [Confections], [Dairy Products],
[Grains/Cereals], [Meat/Poultry],[Produce],[Seafood]
FROM
(
SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
FROM Products P
INNER JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
INNER JOIN Categories C
on C.CategoryID=P.CategoryID
) PIV
PIVOT (SUM(Monto) FOR CategoryName IN ([Beverages], [Condiments], [Confections], [Dairy Products],
[Grains/Cereals], [Meat/Poultry],[Produce],[Seafood])) AS Child
Having executed the code above, we will get this result pivoted.
Finally, using dynamic PIVOT the result will be the same.
DECLARE @CatPVT AS NVARCHAR(MAX), @Categorias AS VARCHAR(20)
DECLARE @CatID INT
SET @CatID=(SELECT MIN(CategoryID) FROM Categories)
SET @Categorias = ( SELECT CategoryName FROM Categories WHERE CategoryID = @CatID)
SET @CatPVT = N''
WHILE @Categorias IS NOT NULL
BEGIN
SET @CatPVT = @CatPVT + N',['+ @Categorias +N']'
SET @Categorias = (SELECT TOP(1) CategoryName
FROM Categories WHERE CategoryID > @CatID
ORDER BY CategoryID ASC)
SET @CatID=(SELECT MIN(CategoryID) FROM Categories WHERE Categoryname=@Categorias)
END
print @CatPVT
SET @CatPVT = SUBSTRING(@CatPVT, 2, LEN(@CatPVT))
DECLARE @sql AS NVARCHAR(MAX)
SET @sql = N'SELECT *
FROM (SELECT P.ProductID, C.CategoryName, (OD.UnitPrice * OD.Quantity) AS Monto
FROM Products P
INNER JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
INNER JOIN Categories C
ON C.CategoryID=P.CategoryID
) PIV
PIVOT (SUM(Monto) FOR CategoryName IN ('+ @CatPVT + ')) AS Child'
EXEC sp_executesql @sql
As you have seen, dynamic PIVOT is truly useful for a real business case. Therefore, I hope you make the most out of this algorithm. That is all for now, let me know any remarks you may have. Thanks for reading. Stay tuned.
