En Firebird nativamente no tenemos la posibilidad de crear cross-tables pero es muy fácil simular una.

¿Qué es una cross-table?

Una tabla que puede ser leída de arriba hacia abajo o de izquierda a derecha y en ambos casos nos provee de información relevante. También se la conoce como pivot table y como tabla multidimensional.

Una tabla normal solamente se lee de arriba hacia abajo ya que si la leemos de izquierda a derecha nos estamos refiriendo siempre al mismo registro. En cambio en una cross-table podemos pensar que cada fila es un registro y también que cada columna es un registro.

¿Cuál es la ventaja de usar una cross-table?

Sumarizar y mostrar grandes cantidades de datos. Una cross table siempre tiene menos filas (generalmente muchísimas menos) que las tablas de donde se obtuvieron sus datos.

Ejemplo:

Queremos mostrar las ventas de cada producto que ocurrieron los días 15/02/2011, 16/02/2011 y 17/02/2011. Por supuesto que algunos productos pudieron haberse vendido muchas veces durante esos días, con Facturas diferentes, y algunos productos no haberse vendido.

Tenemos una tabla PRODUCTOS con los datos de los productos que nuestra empresa vende.

CROSS2

Captura 1. Si haces clic en la imagen la verás más grande

Tenemos una tabla MOVIMCAB (cabecera de movimientos) con estos datos, correspondientes a las ventas realizadas:

CROSS1

Captura 2. Si haces clic en la imagen la verás más grande

Y una tabla MOVIMDET (detalles de movimientos) con los datos de los productos vendidos:

CROSS3

Captura 3. Si haces clic en la imagen la verás más grande

Esta tabla tiene 6 filas porque cada una corresponde a una línea de una Factura. Si nos fijamos en la columna MOV_IDEPRD (identificador del producto) el número 13 está en 3 filas, lo cual significa que ese producto se vendió 3 veces.

Una consulta normal para saber los datos de los productos vendidos sería como ésta:

SELECT
   MVC_FECHAX,
   MVC_NRODOC,
   PRD_NOMBRE,
   MOV_CANTID,
   MOV_PRECIO,
   MOV_CANTID * MOV_PRECIO AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI

Y así obtendríamos este resultado:

CROSS4

Captura 4. Si haces clic en la imagen la verás más grande

O sea, 6 productos vendidos, 6 filas en el resultado. Si un producto se vendió más de una vez entonces se mostrará más de una vez. Podríamos agrupar esos datos por producto y fecha, como muestra la siguiente consulta:

SELECT
   MVC_FECHAX,
   PRD_NOMBRE,
   SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
FROM
   MOVIMDET
JOIN
   MOVIMCAB
      ON MOV_IDECAB = MVC_IDENTI
JOIN
   PRODUCTOS
      ON MOV_IDEPRD = PRD_IDENTI
GROUP BY
   2, 1

y éste sería el resultado:

CROSS5

Captura 5. Si haces clic en la imagen la verás más grande

Pero el día 15/02/2011 se vendieron 4 productos distintos y por lo tanto hay 4 filas. Y el producto COCA COLA 350 C.C. se vendió en los dos días y por lo tanto está repetido. Podríamos agregarle la cláusula HAVING a la consulta anterior pero en ese caso las filas estarían agrupadas por fechas o por nombres de los productos y tampoco obtendríamos lo que estamos buscando.

¿Cuál es la solución?

Usar una cross-table.

WITH VENTASDIA AS (
   SELECT
      MOV_IDEPRD,
      MVC_FECHAX,
      SUM(MOV_CANTID * MOV_PRECIO) AS TOTALPRODUCTO
   FROM
      MOVIMDET
   JOIN
      MOVIMCAB
         ON MOV_IDECAB = MVC_IDENTI
   GROUP BY
      1, 2
)

SELECT
   PRD_IDENTI,
   PRD_NOMBRE,
   V1.TOTALPRODUCTO AS "15/02/2011",
   V2.TOTALPRODUCTO AS "16/02/2011",
   V3.TOTALPRODUCTO AS "17/02/2011"
FROM
   PRODUCTOS
LEFT JOIN
   VENTASDIA V1
      ON PRD_IDENTI = V1.MOV_IDEPRD AND
         V1.MVC_FECHAX = '02/15/2011'
LEFT JOIN
   VENTASDIA V2
      ON PRD_IDENTI = V2.MOV_IDEPRD AND
         V2.MVC_FECHAX = '02/16/2011'
LEFT JOIN
   VENTASDIA V3
      ON PRD_IDENTI = V3.MOV_IDEPRD AND
         V3.MVC_FECHAX = '02/17/2011'
WHERE
   V1.TOTALPRODUCTO IS NOT NULL OR
   V2.TOTALPRODUCTO IS NOT NULL OR
   V3.TOTALPRODUCTO IS NOT NULL

Al ejecutar esa consulta, éste será el resultado:

CROSS6

Captura 6. Si haces clic en la imagen la verás más grande

Conclusión:

Las cross-tables pueden ser extremadamente útiles cuando necesitamos mostrar datos sumarizados porque la información que contienen está sumarizada tanto si se lee de arriba hacia abajo como si se lee de izquierda a derecha. Al leer de izquierda a derecha generalmente el valor de las columnas es obtenido a través de alguna función agregada (SUM(), MAX(), MIN(), COUNT(), AVG() aunque por supuesto no siempre debe ser así).

Artículos relacionados:

Tablas agregadas

El índice del blog Firebird21