Curso de SQL

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

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:

2. Pedidos:

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:

Comentarios a la consulta:

La cl�sula 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 al 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");