Referencias Cruzadas en SQL

Qué son las referencias cruzadas, cómo se construyen y para qué se utilizan.
Una consulta de referencias cruzadas es aquella que nos permite visualizar los datos en filas y en columnas, estilo tabla, por ejemplo:

Producto / Año19961997
Pantalones1.250 3.000
Camisas8.560 1.253
Zapatos4.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 agregadaEs una función SQL agregada que opera sobre los datos seleccionados.
instrucción selectEs una instrucción SELECT.
campo pivotEs 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-1998# And #12-31-1998#
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-1998# And #12-31-1998#
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:


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, Artículos
WHERE
   Pedidos.Id = Artículos.Id
GROUP BY
    Pedidos.Id, Artículos.Nombre
PIVOT
    Year(Fecha)

Y obtenemos el siguiente resultado:

ProductoCódigoTotalMedia19961997
Zapatos 13488730048
Pantalones2955238,75375580
Blusas319404856201320

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á basándose en 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 en la 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 mismo 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 de los que extraemos la información: pedidos.cantidad y artículos.nombre, por ello 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");

Autor

Claudio

Compartir

Comentarios

Esteban

04/4/2006
Este artículo no es de ese autor.

omar poblete

19/4/2006
ESTE EJEMPLO NO FUNCIONA !!!!!

Federico

20/4/2006
En access se que anda pero no lo hace en sqlserver. Como logro ese tipo de resumenes en sql?
Muchas gracias por compartir la informacion ;)

Eduardo

20/4/2006
1.-TRANSFORM existe en Access 2000 en adelante
2.-TRANSFORM no aparece en libros "A fondo MS SQL Server 2000"
3.-En el ejemplo, hay que corregir la sintaxis del INNER JOIN

michelle

12/9/2007
Eso que deberian de dar más informacion la verdad eso no nos sirve de nada en ligar de poner ejemplos deberian escribir lo que son y para que sirven con sus ideas
saludo a la sec tec. 66

karen

12/9/2007
te amo erik eres el amor de mi vida soy una tonta por amarte
pero ps ya pelame
yo te amo con toda mi alma
eso no sirve para ti prefieres que sean malas e infieles

Leopoldo Olguín Losoya

25/10/2007
Deven de explicarlo mas brebe por q asi no se le entiende bola de tarugos

Scarlett

12/4/2009
????????
no entendi nada!!!!!!!!! esto apesta y no es nada claro, prefiero mil veces algo resumido!!!!!!!!!!!! aparte la letra es muy chica y me ago biscos!!!

guadalupe

20/4/2009
criterio
porque no colocan acercan sobre del navicat 8 para oracle asi seria de mucho beneficio para muchos

karlosystem

30/4/2009
excelente articulo
gracias me sirvio de mucho !!, pude hacer una tabla de referencia cruzada utilizando esta funcion antes desconocida para mi "PIVOT" desarrollando un reporte de gastos de pension por meses de alumnos.. y no hagas caso a los comentarios negativos de estos ignorantes, si no lo practican es claro que nunca sabran realizarlo .. a estas mismas personas querran tambien que se les enseñe a manejar el mouse jajaja.. por que no se dedican a lo que saben estos ignorantes .. me pregunto.. vayan a jugar sus videojuegos alli si les aplaudo.. jajaa. ademas de enseñarles a escribir .. una ortografia pesima!!

Luis

24/5/2009
Opinion
Sencillamente, BUENÍSIMO¡¡¡¡

yuriko

17/9/2009
esta informacion es un asco
podrias dejar de meter informacion inutil si crees que eso sirve estas muy equivocado dedicate a otra cosa y no a estar de flojo pegado a tu compu eres un inutil ni para dar buena informacion sirves torpe estupido y baboso haci que muchas gracias por tu informacion bobas al igual que tu

SANDRA

17/9/2009
tu apestas
eres de lo peor eso no tiene nada k yo le pueda entender cuando kieas as otra referencia sssssiiiiiiiiiiiiiiiiiiii tonta ash como hay gente ignorante burraaaaaaaaaaaaaa estupida ash pendeja en pocas palabras

Thecritic

08/6/2010
No es TSQL
El ejemplo NO SIRVE para SQL server
it sucks

Christiam

18/6/2010
Stored proceure para pivotear
El código del SP está aquí
http://sqlexperto.obolog.com/pivoteo-datos-517666

El primer ejemplo propuesto lo ejecutaríamos así:

exec sp_Query_Pivot
'Pedidos',
'Producto',
'month(fecha)',
'select month(fecha) from Pedidos group by month(fecha) order by month(fecha)',
'sum',
'Cantidad',
'',
'Pedido',
1


El SP no incluye la cláusula WHERE, debido a que en teoría, antes de realizar el pivoteo, ya hemos filtrado la información

Santiago Pinto

21/7/2010
Bien
Gracias por el aporte, yo queria saber como le hago andar a esta consulta si tengo access 2003 y estoy programando en VB 6.0.

Ismael Montes

22/9/2010
Consulta Referencias cruzadas en sql
me gustaria saber como hacer para obtener estos datos en forma de tabla utilizando html en la consulta

Ismael Montes

22/9/2010
Mi criterio sobre los comentarios
Muy buenas tardes a todos:
He esta leyendo los comentarios de muchos y me parece que para aquellos cuya opinion es totalmente negativa acerca de la información contenida en los articulos que se puedan encontrar en internet (foros, tutoriales, etc.), lo mejor es que se la reserven para si y no lo escriban puesto que en estos casos resultan ser dañinos para quien escribe el articulo y para el resto de lectores les hace perder el tiempo leyendo comentarios que en lugar de ayudar mas bien destruyen el entusiasmo

fernando

05/4/2011
Vaya con la gente
¡Qué destructora es la gente! Si no entendeis el artículo, lo podeis decir de buenas maneras, para que el autor intente mejorarlo y le ponga más detalles. No me parece nada lógico que una persona intente enseñar algo de manera altruïsta y la gente se le tire encima insultando. Seguro que esos mismos no han aportado nada al mundo en su vida.. sólo chupan de la gente trabajadora y emprendedora.

Neil

23/1/2012
EN TEXTO FUNCIONA
Es decir, si tengo una tabla de ventas por tipo de vestimenta.
producto tipo ventas
pantalon largo 2
pantalon mediano 3
pantalon corto 4
falda largo 5
falda mediano 6
falda corto 7

y quisiera que la tabla cruzada me muestre
largo mediano corto
pantalon
falda

es posible????

gracias
Neil Lopez
nlopez@lukoll.com.pe