El presente artículo, que cierra nuestro monográfico dedicado a la construcción y usos prácticos de las tablas numéricas auxiliares o tally tables, se orienta a la utilización de esta técnica como elemento de soporte en la construcción de sentencias que utilicen el operador PIVOT, y en las cuales debamos generar un número considerable de columnas que sigan un determinado patrón, ahorrando por consiguiente una importante cantidad de escritura de código manual. Comencemos pues por una sencilla introducción que nos proporcione una idea aproximada de la situación a resolver.
Partimos de la siguiente consulta contra la tabla FactInternetSales, perteneciente a la base de datos AdventureWorksDW2012, que muestra las cifras de ventas por código territorial y año.
SELECT SalesTerritoryKey, YEAR(OrderDate) AS Anualidad, SalesAmount FROM FactInternetSales
Necesitamos adaptar esta sentencia, de forma que mediante una operación de pivotado, creemos tantas columnas como años existan en OrderDate. Sumando para cada combinación de SalesTerritoryKey y año, el valor de la columna SalesAmount, tal y como describe el esquema de la siguiente figura.
Teniendo en cuenta estos requerimientos, la consulta con PIVOT quedaría de la siguiente forma.
SELECT SalesTerritoryKey, [2005], [2006], [2007], [2008] FROM ( SELECT SalesTerritoryKey, SalesAmount, YEAR(OrderDate) AS Anualidad FROM FactInternetSales ) AS tblDatosOrigen PIVOT ( SUM(SalesAmount) FOR Anualidad IN ( [2005], [2006], [2007], [2008] ) ) AS tblPivot
Si además quisiéramos agrupar las cifras de ventas por los productos que pertenecieran a las subcategorías de bicicletas, añadiríamos a la sentencia las tablas DimProduct y DimProductSubcategory, combinándolas para obtener los nuevos datos a mostrar.
SELECT EnglishProductSubcategoryName, [2005], [2006], [2007], [2008] FROM ( SELECT FIS.SalesAmount, YEAR(FIS.OrderDate) AS Anualidad, DPS.EnglishProductSubcategoryName, DPS.ProductSubcategoryKey FROM FactInternetSales AS FIS INNER JOIN DimProduct AS DP ON FIS.ProductKey = DP.ProductKey INNER JOIN DimProductSubcategory AS DPS ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3 ) AS tblDatosOrigen PIVOT ( SUM(SalesAmount) FOR Anualidad IN ( [2005], [2006], [2007], [2008] ) ) AS tblPivot;
Hasta aquí todo bien, la cantidad de código a escribir no es muy elevada, pero ahora se nos plantea un nuevo requisito, consistente en mostrar los resultados de ventas por año y mes, lo que supone modificar la sentencia para añadir al código toda la combinatoria de columnas de año y mes, como vemos a continuación.
SELECT EnglishProductSubcategoryName, [2005_1], [2005_2], [2005_3], [2005_4], [2005_5], [2005_6], [2005_7], [2005_8], [2005_9], [2005_10], [2005_11], [2005_12], [2006_1], [2006_2], [2006_3], [2006_4], [2006_5], [2006_6], [2006_7], [2006_8], [2006_9], [2006_10], [2006_11], [2006_12], [2007_1], [2007_2], [2007_3], [2007_4], [2007_5], [2007_6], [2007_7], [2007_8], [2007_9], [2007_10], [2007_11], [2007_12], [2008_1], [2008_2], [2008_3], [2008_4], [2008_5], [2008_6], [2008_7], [2008_8], [2008_9], [2008_10], [2008_11], [2008_12] FROM ( SELECT FIS.SalesAmount, CONVERT(varchar(4), YEAR(FIS.OrderDate)) + '_' + CONVERT(varchar(2), MONTH(FIS.OrderDate)) AS AnualidadMes, DPS.EnglishProductSubcategoryName FROM FactInternetSales AS FIS INNER JOIN DimProduct AS DP ON FIS.ProductKey = DP.ProductKey INNER JOIN DimProductSubcategory AS DPS ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3 ) AS tblDatosOrigen PIVOT ( SUM(SalesAmount) FOR AnualidadMes IN ( [2005_1], [2005_2], [2005_3], [2005_4], [2005_5], [2005_6], [2005_7], [2005_8], [2005_9], [2005_10], [2005_11], [2005_12], [2006_1], [2006_2], [2006_3], [2006_4], [2006_5], [2006_6], [2006_7], [2006_8], [2006_9], [2006_10], [2006_11], [2006_12], [2007_1], [2007_2], [2007_3], [2007_4], [2007_5], [2007_6], [2007_7], [2007_8], [2007_9], [2007_10], [2007_11], [2007_12], [2008_1], [2008_2], [2008_3], [2008_4], [2008_5], [2008_6], [2008_7], [2008_8], [2008_9], [2008_10], [2008_11], [2008_12] ) ) AS tblPivot;
Como acabamos de comprobar, el volumen de código a escribir se ha incrementado sustancialmente debido a la necesidad de especificar las columnas que contienen las ventas por año y mes. En el caso de que debamos desarrollar varios escenarios de similares características, su mantenimiento puede convertirse en una tarea bastante molesta y propensa a incurrir en errores. Por este motivo, el uso de una tabla numérica como herramienta de ayuda en la elaboración del código puede resultar una opción a tener en cuenta.
En primer lugar necesitamos la mencionada tabla numérica con base 1, a la que denominaremos Numeros (al igual que en el resto de artículos que sobre este tema hemos dedicado en el blog), conteniendo al menos 2008 filas, ya que se trata del máximo valor para el año en la columna OrderDate de la tabla FactInternetSales.
Seguidamente crearemos una sentencia basada en dos expresiones de tabla (CTE) contra la tabla numérica. La primera devolverá el rango de años existente en la columna OrderDate y la segunda los números de mes. A continuación cruzaremos ambas expresiones mediante CROSS JOIN, para comprobar que obtenemos correctamente las combinaciones de año y mes.
WITH cteAnualidades AS ( SELECT NumeroID AS Anualidad FROM Numeros WHERE NumeroID BETWEEN (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales) AND (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales) ), cteMeses AS ( SELECT NumeroID AS Mes FROM Numeros WHERE NumeroID BETWEEN 1 AND 12 ) SELECT Anualidad, Mes FROM cteAnualidades CROSS JOIN cteMeses ORDER BY Anualidad,Mes
Esta consulta la integraremos posteriormente en otra de tipo dinámico, siendo esta última la que contenga el operador PIVOT. Para recoger y dar formato al resultado, en aquella parte de la sentencia que devuelve el cruce de años y meses emplearemos una variable de cadena (@sAnualidadesMeses) que reutilizaremos en dos ocasiones dentro de la construcción de la consulta principal. Veamos en primer lugar el código necesario para obtener la cadena con las definiciones de columna año-mes.
DECLARE @sSQL AS nvarchar(2000); DECLARE @sAnualidadesMeses AS nvarchar(1000); SET @sAnualidadesMeses = ''; WITH cteAnualidades AS ( SELECT NumeroID AS Anualidad FROM Numeros WHERE NumeroID BETWEEN (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales) AND (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales) ), cteMeses AS ( SELECT NumeroID AS Mes FROM Numeros WHERE NumeroID BETWEEN 1 AND 12 ) SELECT @sAnualidadesMeses += '[' + CONVERT(varchar(4), Anualidad) + '_' + CONVERT(varchar(2), Mes) + '],' FROM cteAnualidades CROSS JOIN cteMeses; PRINT @sAnualidadesMeses;
Y ahora, la sentencia al completo.
DECLARE @sSQL AS nvarchar(2000); DECLARE @sAnualidadesMeses AS nvarchar(1000); SET @sAnualidadesMeses = ''; WITH cteAnualidades AS ( SELECT NumeroID AS Anualidad FROM Numeros WHERE NumeroID BETWEEN (SELECT MIN(YEAR(OrderDate)) FROM FactInternetSales) AND (SELECT MAX(YEAR(OrderDate)) FROM FactInternetSales) ), cteMeses AS ( SELECT NumeroID AS Mes FROM Numeros WHERE NumeroID BETWEEN 1 AND 12 ) SELECT @sAnualidadesMeses += '[' + CONVERT(varchar(4), Anualidad) + '_' + CONVERT(varchar(2), Mes) + '],' FROM cteAnualidades CROSS JOIN cteMeses; SET @sAnualidadesMeses = LEFT(@sAnualidadesMeses, LEN(@sAnualidadesMeses) - 1); SET @sSQL = 'SELECT EnglishProductSubcategoryName,' + @sAnualidadesMeses + ' FROM (SELECT FIS.SalesAmount,' + 'CONVERT(varchar(4), YEAR(FIS.OrderDate)) + ''_'' + CONVERT(varchar(2),MONTH(FIS.OrderDate)) AS AnualidadMes,' + 'DPS.EnglishProductSubcategoryName ' + 'FROM FactInternetSales AS FIS ' + 'INNER JOIN DimProduct AS DP ' + 'ON FIS.ProductKey = DP.ProductKey ' + 'INNER JOIN DimProductSubcategory AS DPS ' + 'ON DP.ProductSubcategoryKey = DPS.ProductSubcategoryKey ' + 'WHERE DPS.ProductSubcategoryKey BETWEEN 1 AND 3) AS tblDatosOrigen ' + 'PIVOT (' + 'SUM(SalesAmount) ' + 'FOR AnualidadMes IN (' + @sAnualidadesMeses + ')) AS tblPivot'; EXECUTE sp_executesql @sSQL;
Comprobaremos que hemos obtenido el mismo resultado pero escribiendo una menor cantidad de código.
Podemos concluir, una vez revisadas las aplicaciones prácticas que hemos ido describiendo tanto en el presente como en los anteriores artículos sobre las tablas numéricas auxiliares, que representan una poderosa herramienta con la que contar en nuestro arsenal de utilidades para SQL Server. Espero que en alguna ocasión le resulten de ayuda al lector en su trabajo.
Deja un comentario