USE AdventureWorks GO SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader ORDER BY CustomerID
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
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
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
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
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
