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 Year TotalDue
----------- ----------- ---------------------
1 2001 14603,7393
1 2001 26128,8674
1 2002 37643,1378
1 2002 34722,9906
2 2002 10184,0774
2 2002 5469,5941
2 2003 1739,4078
2 2003 1935,5166
2 2003 3905,2547
2 2003 4537,8484
2 2004 4053,9506
2 2004 908,3199
3 2004 17051,8292
3 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 2001 2002 2003 2004
----------- --------------------- --------------------- --------------------- ---------------------
1 40732,6067 72366,1284 NULL NULL
2 NULL 15653,6715 12118,0275 4962,2705
3 39752,8421 168393,7021 219434,4265 51925,3549
4 NULL 263025,3113 373484,299 143525,6018
5 NULL 33370,6901 60206,9999 20641,1106
6 NULL NULL 668,4861 2979,3473
7 NULL 6651,036 3718,7804 NULL
8 NULL NULL 19439,2466 10900,0347
9 NULL 320,6283 11401,5975 5282,8652
10 NULL 96701,7401 291472,2172 204525,9634
11 40350,4474 24300,4254 NULL NULL
12 NULL 117419,735 191505,7911 29091,7653
14 NULL NULL 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'
EXEC 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 JOIN dbo.[Order Details] OD
ON P.ProductID=OD.ProductID
INNER JOIN Categories C
ON C.CategoryID=P.CategoryID
GO
Estos serán los resultados:
ProductID CategoryName Monto
----------- ------------------ ------------------
11 Dairy Products 168,00
42 Grains/Cereals 98,00
72 Dairy Products 174,00
14 Produce 167,40
51 Produce 1696,00
41 Seafood 77,00
51 Produce 1484,00
65 Condiments 252,00
22 Grains/Cereals 100,80
57 Grains/Cereals 234,00
65 Condiments 336,00
20 Confections 2592,00
33 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 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
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):
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 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
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,