Colaboraciones en el Guille

Consulta de referencias cruzadas en SQL Server 2000

 

Fecha: 06/Jun/2006 (05/06/2006)
Autor: Marco Antonio Padierna - mpadierna@yahoo.com.mx

 


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:


  -- 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)


ir al índice principal del Guille