Tablas numéricas auxiliares (Tally Tables) en acción. Creación dinámica de sentencias con PIVOT

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

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_01

 

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.

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_02

 

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

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_03

 

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;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_04

 

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;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_05

 

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

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_06

 

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;

TablasNumericasAuxiliaresTallyTableEnAccionCreacionDinamicaSentenciasPIVOT_07

 

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

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *