Tema 7. Tablas de referencias cruzadas (I)

Introducción

Cuando queremos representar una consulta sumaria con dos columnas de agrupación como una tabla de doble entrada en la que cada una de las columnas de agrupación es una entrada de la tabla utilizaremos una consulta de tabla de referencias cruzadas.

Por ejemplo queremos obtener las ventas mensuales de nuestros empleados. Tenemos que diseñar una consulta sumaria calculando la suma de los importes de los pedidos agrupando por empleado y mes de la venta.

La consulta sería:

SELECT rep as empleado, month(fechapedido) as mes, sum(importe) as vendido
FROM pedidos
GROUP BY rep, month(fechapedido)

El resultado sería la tabla que aparece a la derecha:

La consulta quedaría mucho más elegante y clara presentando los datos en un formato más compacto como el siguiente:

Pues este último resultado se obtiene mediante una consulta de referencias cruzadas. Observar que una de las columnas de agrupación (rep) sigue definiendo las filas que aparecen (hay una fila por cada empleado), mientras que la otra columna de agrupación (mes) ahora sirve para definir las otras columnas, cada valor de mes define una columna en el resultado, y la celda en la intersección de un valor de rep y un valor de mes es la columna resumen, la que contiene la función de columna (la suma de importe).

Las consultas de referencias cruzadas se pueden crear utilizando el asistente, es mucho más cómodo pero es útil saber cómo hacerlo directamente en SQL por si queremos variar algún dato una vez realizada la consulta con el asistente o si queremos definir una consulta de referencias cruzadas que no se puede definir con el asistente.

La sentencia TRANSFORM

La sentencia TRANSFORM es la que se utiliza para definir una consulta de referencias cruzadas.

La sintaxis es la siguiente:

Resultado es la función de columna que permite obtener el resultado de las celdas.

En la SELECT la columna fija es la columna que define el encabezado de filas, el origen que indicamos en la cláusula FROM es la tabla (o tablas) de donde sacamos la información, y en la cláusula GROUP BY ponemos la columna que va a definir las filas del resultado.

La SELECT puede contener una cláusula WHERE para seleccionar la filas que se utilizan para calcular el resultado, puede contener subconsultas pero no la cláusula HAVING.

En la cláusula PIVOT indicamos la columna cuyos valores van a definir columnas dinámicas del resultado a esta columna la llamaremos pivote.

La cláusula IN permite definir el conjunto de valores que queremos que aparezcan como columnas dinámicas.

Es conveniente que la columna pivote que sirve de encabezado de columna tenga un número limitado de posibles valores para que no se generen demasiadas columnas. En nuestro ejemplo es mejor utilizar el mes como encabezado de columna y no de fila ya que posibles empleados hay muchos más y además el mes toma valores que conocemos y podemos utilizar por lo tanto la cláusula IN para que aparezcan todos los meses del año.

En nuestro ejemplo resultado sería SUM(importe), la columna fija es rep con un alias para que salga la palabra empleado en el encabezado, el origen de datos es la tabla pedidos (porque el resultado SUM(importe) se obtiene de pedidos), la columna del GROUP BY es rep ya que queremos una fila por cada representante, la columna dinámica, la que ponemos en la cláusula PIVOT sería MONTH(fechapedido).

La sentencia quedaría de la siguiente forma:

TRANSFORM Sum(importe)
SELECT rep as empleado
FROM pedidos
GROUP BY rep
PIVOT month(fechapedido)

Lo mejor para montar una consulta de referencias cruzadas en SQL es pensar la sumaria normal y luego distribuir los términos según corresponda.