Consultas de Referencias Cruzadas

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

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
Claudio Casares
Redactor del tutorial de SQL
LORE
5/12/2006

DEFINIR MEJOR Y CON POCAS PALABRAS A MODO DE RESUMEN

p.cesar
1/02/2007

no entendi nada,expliquenlo mas mejor ok

Claudia Cruz jfrtlol
23/02/2007

que todo lo que escriben no se entiende

mAURICIO
9/03/2007

DEMASADO DESORDENADO

karina
12/03/2007

bien exelete

no le entiendo nada expliquenlo mas mejor eeeeeeeeeee deberas estan bien guarros

kleber
21/03/2007

soy gay

monika perez tlapanco
30/08/2007

ser mas explicitos en los temas

deberas esta bien desordenado y bien naco

chalan
6/09/2007

deben de poner lo k les piden no otro pedo como un diccionario

jose
10/09/2007

no le entindi por k yo buscaba k caracteristicas tiene y no le entendia

jerson
17/09/2007

puro pachekos de escobedo jaja so ke

lanoentiendo
24/09/2007

no entiendo, no entiendo, no entiendo… ¿porque no mas claro? esta muy indesifrable, no entiendo, no entiendo, no entiendo… ¿mencione que no entiendo?

selene
27/09/2007

bueno me estas hola soy de monterry y hola a todos jaja

LISETH
13/10/2007

NO ENTIENDO NADA SOBRE LAS REFERENCIAS CRUZADAS

no entiendo nada e por que no explican mejor
yo no entiendo nada y eso es tarea que nos encargan en la escuela

azaeel
26/11/2007

yo busco sobre referencias cruzadas en la rea li dad y ejemplos

nole entiendo nada

freddy
11/12/2007

chvr muy wena y precisa la explicacion sigue asiiii……¿que es una consulta de referencia cruzada?¿… va

ezdas
6/03/2008

no le hagan casos a estos niños que deberian de preocuparse por entenderles mas a las cosas
creo que esta muy bien el ejemplo me sirvio para agrupar unos datos que no podia agrupar por mes y categoria

chale no les entendiendo explikense nop chale esta kañon los kiero a todos en especial a ….y a …… y tambien a ….

mane el joto
19/05/2008

estan bien pendejos nomamen yobuscaba funciones yno encontre nada

Carlos
19/05/2008

Est claro pero cuando lo corro en Sql 200 no pasa naha me sale este emnsaje

Servidor: mensaje 170, nivel 15, estado 1, línea 1
Línea 1: sintaxis incorrecta cerca de ‘Pedidos’.
Servidor: mensaje 170, nivel 15, estado 1, línea 5
Línea 5: sintaxis incorrecta cerca de ‘PIVOT’.

la bella
8/07/2008

esta super

la bella
8/07/2008

carlos no digas mamadas

alberto2236
14/08/2008

No mamen si van solo a copiar y pegar minimo aganlo bien.

Este es el texto, si no el original, por lo menos se entiendo, no como estas tonterias que hizo claudio

monica
27/08/2008

ni les entiendo y no aprarecen nada en los ejemplos q quiero

emily
2/09/2008

soy una princesita aa y esta padre la pagina se entiende muy bien

emily
2/09/2008

son unos BURROS los que no le entienden
bye
kises muua

son unos burrote lo k tienem k poner son ejemplos los odio x k no me dan lo k yo kiero
putitos son unos vale mierda ps no saven nada
p
u
t
o
s
lo k yo kiero es un ejemplo
ok
k les kede bien claro
“ejemplosssssssss”
ok osea no manchen
k akaso no saven
bueno ni
mis palabras gastadas
todo lo k
me kanse kon unos
burros
e idiotas komo
ustedes
osea no sean
lusers ssiii
no tienen otra kosa k
aser ps
raskense la
pinchi kola
ok aganme kaso les konviene
si no van a segir
viendo segido
estos mensajes

ATTE: paola

bay k te vaya muy,muy
pero muy
malllllllllll
jajajajajajaja
jajajajajajaja
jajajajajajaja
bay
aber kuando les
buelbo
aa escribir
ok ok ok ok ok
muuuuaa

jose carlos blanco millan
10/09/2008

pinches putos pendejos

No entendii nada….xD
expliquense mejorr porfa

No manches esto esta del naboo…

es q quiero encontrar esta cosaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

y q me cuentan
estoy en busca de un novio:
q sea guapo
guero alto pelo largo castaño ojos verdes de unos 2 metros nada mas y q sea mi principe azul gracias contestenme

jesus
23/09/2008

no manches mejor explikense bien no bueno bye esto no se entiende nada solo keria saber ke es una referencia cruzada y ustedes con sus mamadas para la otra una explicacion mas breve no?.

vero
23/09/2008

NO OSEA ESTA DEL NABO SUS PIN…. COMENTARIOS EHH.
YA NO DEJENSE DE BABOSADAS Y AGAN UN RESUMEN MAS BREVE.

PINC….. PAGINA ESTA DE POCAS PALABRAS YO SOLO KERIA SABER KE ERA UNA FRECUENCIA CRUZADA? Y USTEDES ME SALEN CON ESTO NOMANCHEZ OSEA BUSQUENSE UN BOSQUE Y PIERDANSE….

eveLynee
14/10/2008

nno entiienddoo NADA!
Oseeaa expLiiqennsee nnaqooossss assshhhh mmee chooqqaaannn ¬¬

putos no hay ejemplo claros

soooooooon bien putossssss

victor david ramirez curiel
4/11/2008

son bien putos y no estan los condones que les pedi

pues la neta no entiendo nada y noma s consulto porq e es tarea importante la insofacta

esta
toda
pendeja
la
31
como
ke
no
lo
encuentras
pues
ke
estas
pendeja
o
ke
pedo
si
no
sabes
buscar
aki
pues
vete
a
otra
puta
pagina
jajajaja
pero
pues
de
todas
maneras
vete
a
la
verga
sale
y
si
no
quieres
esto
metete
a
otra
pagina
como
eres
pendeja

Se me hace algo complicado entender

juan daniel
9/09/2009

no puede ser le hacen caso a estas pendejadas

HOLA… NECESITO SABER EN ACCESS 2003..

1.- TENGO UNA REFERENCIA CRUZADA, EN LA CUAL ME APARECE DESGLOSADO HACIA UN LADO POR CANTIDADES, SIN EMBARGO, CUANDO NO ENCUENTRA UN VALOR ME DEJA LA GRILLA EN BLANCO Y LA IDEA MIA, ES QUE SI NO ENCUNETRA UN VALOR … ME COLOQUE UN CERO ….. YA QUE DESPUES OCUPO ESTA GRILLA PARA REALIZAR RESTA Y SUMA EN OTRA CONSULTA Y CUANDO NO ENCUNETRA UN VALOR (OSEA CUANDO QUEDA EN BLANCO) NO ME REALIZA NINGUNA OPERACION MATEMATICA…

2.- POR LO ANTERIOR, COMO PUEDO COLOCAR UN CERO EN LA REFERNCIA CRUZADA CUANDO NO ENCUENTRA UN VALOR.

MI CORREO [email protected]

NECESITO SU AYUDA… GRACIAS…. OJALA QUE ALGUIEN PUEDA CONTESTAR ….

AnDrEa
18/11/2009

top andrea asi +sexi OcuPo sAcAR
una iformechion y no salio ni madres asi
que esta
pagina
no entiende
loq ue pido
dije
un ejemplo de referencias cruzadas y me
salio otra putada asi que ni sirbe
bueno me boy a ainvertigar en otra
pagina que me sirve ok atte: andrea asi o mas ermoxa ok y ni
quien me lo alegue ok :) :0

soy ceci y amo a miguel aguayo arreola ok
soy amiga de andrea y la apoyo
deveras
es una cosa
fea lo que ponen alas 12 de la noche
alli estamos
buescando y lo que nos sale
con sus chingaderas
no me la mamen se psan :) pero ya quu :( bueno yo soy ceci sexi ok mi correo [email protected] :o (p) (r)

Hernan
16/02/2010

hola necesito un ayuda, agradesco anticipada por la respuesta!

tabla1(camp1,camp2,camp3)

mi pregunta es como hago para ir acumulando el camp1 de todos los registros de la tabla.
no se si podria ser correcto algo asi como
#declare @a int
set @a=0
@a=@a+1
quiero asignarle el camp1 a @a

[...] Siguiente capítulo: Consultas y Referencias Cruzadas [...]

son unos pendejos en cada comando tienen que poner ejemplos para

solo kiero saver ke es una referencia cruzada por las kdicen en esta pagina no son son otras inutiles

Maria Areli
7/09/2010

muy pro muy malo
vallanse a la verga y chinguen a su puta madre a y x cierto
TE AMO OSCAR G

iop
7/10/2010

no se entiende absolutamente NADA!!!!
esa cosa no sirve!!

sofia
2/11/2010

en ESTE COCHINERO… NO ENTIENDO NADA :@
Y ALA OTRA EXPLIQUENSE MEJORR ¡¡¡SIII!!!
QUE TAREADOS SON EN HASER ESTA PAGINAA!!
Y PARA LA OTRA NO AGAN NADAA IDIOTASS!!

256