Pivot Dinamico 3z5f6e

Implementación de PIVOT Dinámico PIVOT es uno de los nuevos operadores incluídos en SQL Server 2005 que podemos usar en la cláusula FROM para rotar filas en columnas y conseguir informes de tabla cruzada en un formato tabular, más presentables, claramente resumidos y descriptivos. En un post anterior tratamos un ejemplo acerca del uso de PIVOT. Bien, la forma cómo se usó PIVOT en dicho ejemplo viene a ser su implementación estática, asi es, vimos cómo implementar PIVOT estático, además se dejó algunos recursos para familiarizarse con el tema. Por ejemplo, usemos la base de datos AdventureWorks y analicemos un poquito el resultado del siguiente query que más adelante pivotearemos:

SELECT CustomerID,YEAR(DueDate) [Year], TotalDue FROM Sales.SalesOrderHeader ORDER BY CustomerID En los resultados podemos apreciar que cada cliente hizo muchas ventas por año, entre los años 2001 y 2004. CustomerID ----------1 1 1 1 2 2 2 2 2 2 2 2 3 3 ...

Year TotalDue ----------- --------------------2001 14603,7393 2001 26128,8674 2002 37643,1378 2002 34722,9906 2002 10184,0774 2002 5469,5941 2003 1739,4078 2003 1935,5166 2003 3905,2547 2003 4537,8484 2004 4053,9506 2004 908,3199 2004 17051,8292 2004 34873,5257 ..... .......(Continúa)

En fin, el uso de PIVOT estático tiene la clara desventaja de limitar en número de columnas desglosadas para mostrar información de la columna pivoteada, por ejemplo, de acuerdo al resultado del query anterior, si queremos pivotear la columna TotalDue, y desglosar la información en 4 columnas, es decir, pivotear TotatlDue para los años del 2001 al 2004, se tendría que especificar cada uno de esos valores dentro de la consulta, cuestión que será sencillo y fácil dado que sólo tendremos que indicar 4 valores.

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 Hasta aquí todo parece estar bien, y claro que es así. He aquí los resultados: CustomerID ----------1 2 3 4

2001 2002 2003 2004 --------------------- --------------------- --------------------- --------------------40732,6067 72366,1284 NULL NULL NULL 15653,6715 12118,0275 4962,2705 39752,8421 168393,7021 219434,4265 51925,3549 NULL 263025,3113 373484,299 143525,6018

5 6 7 8 9 10 11 12 14 ...

NULL NULL NULL NULL NULL NULL 40350,4474 NULL NULL ........

33370,6901 NULL 6651,036 NULL 320,6283 96701,7401 24300,4254 117419,735 NULL ...........

60206,9999 20641,1106 668,4861 2979,3473 3718,7804 NULL 19439,2466 10900,0347 11401,5975 5282,8652 291472,2172 204525,9634 NULL NULL 191505,7911 29091,7653 7348,0162 1446,6848 ............. ......(Continúa)

Sin embargo, hay un problema, ¿Sabes cual es el problema? -> la implementación de PIVOT estáticos no es escalable, ya que los valores de la columna que se desglosa para llenar información de la columna pivoteada puede aumentar, es decir, conforme vaya pasando el tiempo la ventas se seguirán llevando acabo teniendo años superiores al 2004, como 2005, 2006,..., y si quisiera (pasados 2 años por ejemplo) pivotear todas las ventas para todos los años hasta la fecha tendría que modificar el query, modificar e indicar los años adicionales a desglosarse. Otro desventaja sería por ejemplo, ¿Qué pasa si tengo que pivotear un columna que tiene 30 valores diferentes?, pues usando PIVOT estático tendría que estar indicando manualmente cada uno de los 30 valores dentro del query, cosa que se torna fastidiosa, aburrida, y sobre todo trabajosa, y esto es poco, pueden ser más valores, por ejemplo 100, me muero!. Todos estos líos se solucionan mediante PIVOT dinámico, que no viene a ser más que una implementación algoritmica para capturar dinámicamente los valores de la columna a desglosare para la columna pivoteada e insertarlo dentro de la cadena final del query a ejecutarse usando sp_executesql (SQl Dinámico). Dejo el query a su merced!, podeis masticarlo a su gusto!.

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

Los resultados del query con PIVOT dinámico serán los mismos que los conseguidos usando PIVOT estático :D. Funciona!, sí señor! :). Ahora vayamos con otro ejemplo, algo parecido, pero ahora será usando la base de datos Northwind, pivoteamos la columna Monto y lo desglosaremos de acuerdo al CategoryName:

--ESTO ES LO QUE PIVOTEAMOS! SELECT P.ProductID, C.CategoryName, OD.UnitPrice * OD.Quantity AS Monto FROM Products P INNER dbo.[Order Details] OD ON P.ProductID=OD.ProductID INNER Categories C ON C.CategoryID=P.CategoryID Estos serán los resultados: ProductID ----------11 42 72 14 51 41 51 65 22 57 65 20 33 ...

CategoryName Monto ----------------------------------Dairy Products 168,00 Grains/Cereals 98,00 Dairy Products 174,00 Produce 167,40 Produce 1696,00 Seafood 77,00 Produce 1484,00 Condiments 252,00 Grains/Cereals 100,80 Grains/Cereals 234,00 Condiments 336,00 Confections 2592,00 Dairy Products 50,00 ................ .....(Continúa)

Pivoteando el resultado anterior, usando PIVOT "estático":

--PIVOTEO 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 dbo.[Order Details] OD ON P.ProductID=OD.ProductID INNER 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 Seguro que ya hiciste un copy/paste, je je je, y si aún el nivel de compatibilidad de la base de datos Northwind es 80 o inferior de seguro te encontrarás con este error: Msg 325, Level 15, State 1, Line 13 Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel. El operador PIVOT sólo trabaja con base de datos cuyo nivel de compatibilidad mayor o igual a 90 (SQL Server 2005). Northwind es una base de datos creada con SQL Server 2000, y por lo tanto su nivel de compatibilidad es 80. Para solucionar este problema debemos obviamente cambiar dicho nivel de compatibilidad a 90. EXEC dbo.sp_dbcmptlevel @dbname=N'Northwind', @new_cmptlevel=90

Ahora si, volvemos a ejecutar el query y tendremos (Muestro resultados parciales):

ProductID Beverages Condiments Confections Dairy Products Grains/Cereals Meat/Poultry Produce Seafood ------------------------------- --------------------- --------------------- --------------------- -------------------- --------------------- --------------------- --------------------1 14277.60 NULL NULL NULL NULL NULL NULL NULL 2 18559.20 NULL NULL NULL NULL NULL NULL NULL 3 NULL 3080.00 NULL NULL NULL NULL NULL NULL 4 NULL 9424.80 NULL NULL NULL NULL NULL NULL 5 NULL 5801.15 NULL NULL NULL NULL NULL NULL 6 NULL 7345.00 NULL NULL NULL NULL NULL NULL 7 NULL NULL NULL NULL NULL NULL 22464.00 NULL 8 NULL 13760.00 NULL NULL NULL NULL NULL NULL 9 NULL NULL NULL NULL NULL 8827.00 NULL NULL 10 NULL NULL NULL NULL NULL NULL NULL 22140.20 11 NULL NULL NULL 13902.00 NULL NULL NULL NULL 12 NULL NULL NULL 12866.80 NULL NULL NULL NULL 13 NULL NULL NULL NULL NULL NULL NULL 5234.40 14 NULL NULL NULL NULL NULL NULL 8630.40 NULL 15 NULL 1813.50 NULL NULL NULL NULL NULL NULL 16 NULL NULL 18748.05 NULL NULL NULL NULL NULL 17 NULL NULL NULL NULL NULL 3 5482.20 NULL NULL 18 NULL NULL NULL NULL NULL NULL NULL 31987.50 19 NULL NULL 6159.50 NULL NULL NULL NULL NULL 20 NULL NULL 23635.80 NULL NULL NULL NULL NULL Lograremos lo mismo, esta vez usando PIVOT dinámico:

--pivot dinámico 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)) print 'ok' print @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 dbo.[Order Details] OD ON P.ProductID=OD.ProductID INNER Categories C ON C.CategoryID=P.CategoryID ) PIV PIVOT (SUM(Monto) FOR CategoryName IN ('+ @CatPVT + ')) AS Child' EXEC sp_executesql @sql No debo recordarles que dentro de la implementación de PIVOT dinámico podrían usarse tablas temporables, variables del tipo TABLE y/o CTEs en lugar de emplearse subconsultas, en todo caso los resultados serán los mismos, sin embargo el rendimiento podría ser mejor, y la lógica se simplicaría bastante. En fin, es bastante sencillo, y a ver si en otro post explico algo al respecto. Saludos,