8. Consultas de Referencias Cruzadas

Una consulta
de referencias cruzadas es aquella que nos permite visualizar los datos en filas
y en columnas, estilo tabla, por ejemplo:

Producto / Año

1996

1997
Pantalones 1.250 3.000
Camisas 8.560 1.253
Zapatos 4.369 2.563

Si tenemos
una tabla de productos y otra tabla de pedidos, podemos visualizar en total
de productos pedidos por año para un artículo determinado, tal
y como se visualiza en la tabla anterior.

La sintaxis para este tipo de consulta
es la siguiente: 

TRANSFORM función agregada instrucción
select PIVOT campo pivot
[IN (valor1[, valor2[, ...]])]

En donde:

función
agregada: Es una función SQL
agregada que opera sobre los datos seleccionados.

instrucción
select: Es una instrucción SELECT.

campo pivot:
Es el campo o expresión que desea utilizar para crear las cabeceras de
la columna en el resultado de la consulta.

valor1,
valor2: Son valores fijos utilizados para crear las cabeceras
de la columna.

Para resumir
datos utilizando una consulta de referencia cruzada, se seleccionan los valores
de los campos o expresiones especificadas como cabeceras de columnas de tal
forma que pueden verse los datos en un formato más compacto que con una
consulta de selección.

TRANSFORM es opcional pero si se incluye es la primera instrucción de una cadena SQL. Precede a la instrucción SELECT que especifica los campos
utilizados como encabezados de fila y una cláusula GROUP
BY que especifica el agrupamiento de las filas. Opcionalmente
puede incluir otras cláusulas como por ejemplo WHERE,
que especifica una selección adicional o un criterio de ordenación
.

Los valores devueltos en campo pivot se
utilizan como encabezados de columna en el resultado de la consulta. Por ejemplo,
al utilizar las cifras de ventas en el mes de la venta como pivot en una consulta
de referencia cruzada se crearían 12 columnas. Puede restringir el campo
pivot para crear encabezados a partir de los valores fijos (valor1,
valor2) listados en la cláusula opcional IN.

También
puede incluir valores fijos, para los que no existen datos, para crear columnas
adicionales.

Ejemplos:

TRANSFORM Sum(Cantidad) AS Ventas SELECT
Producto, Cantidad FROM
Pedidos WHERE Fecha Between #01-01-98# And #12-31-98# GROUP BY Producto
ORDER BY Producto PIVOT DatePart("m", Fecha);

Crea una consulta de tabla de referencias
cruzadas que muestra las ventas de productos por mes para un año específico.
Los meses aparecen de izquierda a derecha como columnas y los nombres de los
productos aparecen de arriba hacia abajo como filas. 

TRANSFORM Sum(Cantidad) AS Ventas SELECT
Compania FROM Pedidos
WHERE Fecha Between #01-01-98# And #12-31-98# GROUP BY Compania
ORDER BY Compania PIVOT "Trimestre " & DatePart("q",Fecha) In ('Trimestre1',
'Trimestre2', 'Trimestre 3', 'Trimestre 4');

Crea una consulta de tabla de referencias
cruzadas que muestra las ventas de productos por trimestre de cada proveedor
en el año indicado. Los trimestres aparecen de izquierda a derecha como
columnas y los nombres de los proveedores aparecen de arriba hacia abajo como
filas.

Un caso
práctico:

Se trata de resolver el siguiente problema: tenemos una tabla de productos con
dos campos, el código y el nombre del producto, tenemos otra tabla de
pedidos en la que anotamos el código del producto, la fecha del pedido
y la cantidad pedida. Deseamos consultar los totales de producto por año,
calculando la media anual de ventas.

Estructura
y datos de las tablas:

1. Artículos:

ID  Nombre
1 Zapatos
2 Pantalones
3 Blusas

2. Pedidos:

Id Fecha Cantidad
1 11/11/1996 250
2 11/11/1996 125
  11/11/1996 520
1 12/10/1996 50
2 04/05/1996 250
  05/08/1996 100
1 01/01/1997 40
2 02/08/1997 60
  05/10/1997 70
1 12/12/1997 8
2 15/12/1997 520
  17/10/1997 1250

Para resolver la consulta planteamos la siguiente consulta: 

TRANSFORM Sum(Pedidos.Cantidad) AS Resultado
SELECT Nombre AS Producto,
Pedidos.Id AS Código, Sum(Pedidos.Cantidad) AS TOTAL, Avg(Pedidos.Cantidad)
AS Media FROM Pedidos INNER JOIN Artículos ON Pedidos.Id = Artículos.Id
GROUP BY Pedidos.Id, Artículos.Nombre PIVOT Year(Fecha);

y obtenemos el siguiente resultado:

Producto Código TOTAL Media 1996 1997
Zapatatos 1 48 87 00 48
Pantalones 2 955 238,75 75 580
Blusas   1940 485 620 1320

Comentarios
a la consulta:

La cláusula TRANSFORM
indica el valor que deseamos visualizar en las columnas que realmente pertenecen
a la consulta, en este caso 1996
y 1997, puesto que las demás
columnas son opcionales.

SELECT
especifica el nombre de las columnas opcionales que deseamos visualizar, en
este caso Producto, Código, Total y Media,
indicando el nombre del campo que deseamos mostrar en cada columna o el valor
de la misma. Si incluimos una función de cálculo el resultado
se hará en base a los datos de la fila actual y no al total de los datos.

FROM especifica el origen de los datos. La primera tabla que debe figurar es aquella
de donde deseamos extraer los datos, esta tabla debe contener al menos tres
campos, uno para los títulos de la fila, otros para los títulos
de la columna y otro para calcular el valor de las celdas.

En este caso en concreto se deseaba visualizar
el nombre del producto, como el tabla de pedidos sólo figuraba el código
del mismo se añadió una nueva columna en la cláusula select
llamada Producto que se corresponda con el campo Nombre de la tabla de artículos.
Para vincular el código del artículo de la tabla de pedidos con
el nombre del misma de la tabla artículos se insertó la cláusula
INNER JOIN.

La cláusula GROUP
BY especifica el agrupamiento de los registros, contrariamente
a los manuales de instrucción esta cláusula no es opcional ya
que debe figurar siempre y debemos agrupar los registros por el campo del cual
extraemos la información. En este caso existen dos campos del cual extraemos
la información: pedidos.cantidad y artículos.nombre, por ellos
agrupamos por los campos.

Para finalizar la cláusula PIVOT indica el nombre de las columnas no opcionales, en este caso 1996 y 1997 y como
vamos a el dato que aparecerá en las columnas, en este caso empleamos
el año en que se produjo el pedido, extrayéndolo del campo pedidos.fecha.

Otras posibilidades
de fecha de la cláusula pivot son las siguientes:

1. Para
agrupamiento por Trimestres

PIVOT "Tri " & DatePart("q",[Fecha]);

2. Para
agrupamiento por meses (sin tener en cuenta el año)

PIVOT Format([Fecha],"mmm") In ("Ene",
"Feb", "Mar", "Abr", "May", "Jun",
"Jul", "Ago", "Sep", "Oct", "Nov",
"Dic");

3. Para agrupar por días

PIVOT Format([Fecha],"Short Date")

Siguiente capítulo: Consultas
de Unión Interna

banner

  1. Introducción
  2. Consultas
    de Selección
  3. Criterios
    de Selección
  4. Agrupamiento
    de Registros y Funciones Agregadas
  5. Consultas
    de Actualización
  6. Tipos
    de Datos
  7. SubConsultas
  8. Consultas
    y Referencias Cruzadas
  9. Consultas
    de Unión Interna
  10. Consultas
    de Unión Externas
  11. Estructuras
    de las Tablas
  12. Consultas
    con Parámetros
  13. Acceso
    a las Bases de Datos Externas
  14. Omitir los permisos de ejecución
  15. La
    Cláusula Procedure
  16. Anexos