Consulta de referencias cruzadas en SQL Server 2000

1. El problema.

Cuando se trabaja con datos de captura diaria tales como ventas, es común que nos pidan informes de referencias cruzadas, por ejemplo, convertir estos datos

cliente	fecha	importe	... (otras columnas)
JOSE	2006-06-01	1000.0
JOSE	2006-06-02	2000.0
JOSE	2006-06-03	3000.0
LUIS	2006-06-01	12000.0
LUIS	2006-06-02	32000.0
LUIS	2006-06-03	35000.0
LUIS	2006-06-04	12500.0
PACO	2006-06-01	12000.0
PACO	2006-06-02	32000.0
LUZMA	2006-06-01	4000.0
LUZMA	2006-06-02	6300.0

En esto:

Cliente	01/06/2006	02/06/2006	03/06/2006	04/06/2006	Total
JOSE	1000.0	2000.0	3000.0	2500.0	8500.0
LUIS	12000.0	32000.0	35000.0	12500.0	91500.0
LUZMA	4000.0	6300.0	2000.0	1500.0	13800.0
PACO	12000.0	32000.0	35000.0	12500.0	91500.0

 

2. La solución.

Esta propuesta usa tablas físicas para filtrar y convertir los datos, así como para acumular los resultados. Valga decir que el primer intento que hice fue con tablas temporales y tablas en memoria, pero me arrojaba extraños errores al querer eliminar y volver a crear las tablas temporales o al agregar las columnas dinámicamente, en las tablas en memoria.

El primer paso consiste en crear una tabla que contenga solamente las columnas y filas necesarias. De esta manera se optimiza el uso de recursos del servidor. Crearemos una tabla que contenga las columnas cliente, fecha e importe:

if exists (select * from dbo.sysobjects 
  where id = object_id(N'[dbo].[tab1]') and OBJECTPROPERTY(id, N'IsTable') = 1)

  drop table [dbo].[tab1]

  GO

  -- ojo: en esta la fecha es nvarchar

  CREATE TABLE tab1 (cliente nvarchar(50) NOT NULL, fecha nvarchar(10) NOT NULL, 
  importe real NULL)

  GO 

El objetivo de crear la columna fecha como nvarchar es facilitar la creación posterior de columnas en la tabla de resultados.

A continuación se insertan los registros, supóngase que la tabla de origen se llama EJEMPLO:

INSERT INTO tab1 (cliente, fecha, 
  importe) 
  SELECT cliente, CONVERT(nvarchar(10),fecha,103), importe
  FROM ejemplo
  WHERE fecha BETWEEN '01/06/2006' AND '04/06/2006' 

Ahora se crea la tabla de resultados, que en principio incluye solamente los clientes:

if exists (select * from dbo.sysobjects 
  where id = object_id(N'[dbo].[tab2]') and OBJECTPROPERTY(id, N'IsTable') = 1)

  drop table [dbo].[tab2]

  GO

  CREATE TABLE tab2 (Cliente nvarchar(50) NOT NULL)

  GO 

Se insertan los clientes ya filtrados:

INSERT INTO tab2 (Cliente)
  SELECT DISTINCT cliente FROM tab1 

 

El paso siguiente es más complejo e incluye el uso de un cursor para ir leyendo las distintas fechas que se agregarán así como la ejecución de consultas "al vuelo" mediante la instrucción EXECUTE.

La lógica es:

• Consultar las distintas fechas de la tabla filtrada.
• Para cada fecha insertar una columna.
• Actualizar esa columna con el valor de la tabla filtrada, para la fecha seleccionada.

  -- variable para crear las columnas
  DECLARE @encabeza nvarchar(50)

  -- variables para definir las consultas en tiempo de ejecución
  DECLARE @exec1 nvarchar(1024)

  DECLARE @exec2 nvarchar(1024)

  -- declarar el cursor
  DECLARE encabezados CURSOR FOR 
     SELECT DISTINCT fecha FROM tab1

  -- abrir cursor
  OPEN encabezados

  FETCH NEXT FROM encabezados INTO @encabeza

  -- mientras haya datos...
  WHILE @@FETCH_STATUS = 0

    BEGIN
     -- por cada fecha, agregar una columna
     SET @exec1 = 'ALTER TABLE tab2 ADD [' + @encabeza + '] real NULL '
     EXECUTE (@exec1) 

     -- actualizar la columna nueva con los valores
     SET @exec2 = 'UPDATE tab2 SET [' + @encabeza + '] = t1.importe ' +
     ' FROM tab1 t1 INNER JOIN tab2 t2 ON t1.cliente=t2.cliente ' +
     ' WHERE t1.fecha=''' + @encabeza + ''''

     EXECUTE (@exec2)

    -- siguiente registro
    FETCH NEXT FROM encabezados INTO @encabeza
    END
-- cerrar y liberar la memoria del cursor
CLOSE encabezados
DEALLOCATE encabezados 

Nótese el uso de dos comillas simples para delimitar el criterio @encabeza, puesto que es una cadena.

En este momento la tabla ya contiene los datos listos para mostrar, usando un SELECT. Sin embargo, para fines de completar el ejemplo, agregaremos los totales por fila, mediante el uso de una tercera tabla:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tab3]')
  and 

  OBJECTPROPERTY(id, N'IsTable') = 1)

  drop table [dbo].[tab3]

  GO

  CREATE TABLE tab3 (cliente nvarchar(50) NOT NULL, total real NULL)

  GO

  -- insertar clientes y totales
  INSERT INTO tab3 (cliente, total)

  SELECT cliente, sum(importe) FROM tab1 GROUP BY cliente

Se agrega una columna de totales a la tabla de resultados y se actualiza con la tabla recién creada:

-- crear columna de totales
ALTER TABLE tab2 ADD Total real NULL

-- actualizar totales
DECLARE @exec3 nvarchar(1024)

SET @exec3 ='UPDATE tab2 SET total= t3.total ' + 
    ' FROM tab2 t2 INNER JOIN tab3 t3 ON t2.cliente=t3.cliente'
EXECUTE (@exec3) 

Cabe aclarar que esta última consulta se debe realizar en tiempo de ejecución para que se compile bien el procedimiento debido a que la columna TOTAL se agrega dinámicamente. De lo contrario el analizador de consultas detecta que la columna TOTAL no existe y devuelve un error. Por ello se usa la instrucción EXECUTE.

Por último, para mostrar los resultados, solo se ejecuta un SELECT simple:.

SELECT * FROM tab2

 

3. Conclusión.

Se ha demostrado una técnica que combina el uso de tablas temporales y un cursor para crear una tabla de referencias cruzadas donde no se conocen previamente los nombres de las columnas, en un escenario de uso común en la mayoría de las aplicaciones.

La ventaja de ir creando las columnas en orden consiste en que la presentación se simplifica considerablemente, ya que no se requiere realizar complicadas operaciones al mostrar los resultados, por lo que se puede usar un datagrid que cree las columnas automáticamente.

La desventaja de este enfoque consiste en que se tienen que usar tablas físicas para acumular los resultados. Queda pendiente la solución de este problema usando tablas temporales (de la forma #tab1) u objetos en memoria, usando la sintaxis DECLARE @tab1 TABLE (cliente int NOT NUL, fecha nvarchar(10), importe real).

Se anexa un archivo de texto con la consulta, para ejecutar en el analizador. Al final se incluye una nota para convertir la consulta en un procedimiento almacenado. Este se puede usar para ejecutar la consulta desde una aplicación ASP.NET. El conjunto de datos que arroja se puede asignar a un dataset y asignar dicho dataset como origen de datos de un datagrid, bueno supongo que se saben el resto de la historia...

---

Fichero con el código de ejemplo: mpadierna_EjemploTablaCruzada.zip - 3 KB

(MD5 checksum: D33D3A043C826D55773C5288FACF2B87)