simular referencia cruzada en mysql

[pedrodiego]

Le muestro una consulta en SQL que resolvería mi problema, pero por desgracia como verán no funcionaría en MYSQL ya que TRANSFORM y PIVOT no son permitidos.

TRANSFORM Sum(srv_datos.cantidad1+srv_datos.cantidad2+srv_da tos.cantidad3) AS SumaDecantidad1
SELECT srv_servicios.servicio
FROM srv_datos, srv_servicios
WHERE (((srv_servicios.id)=[srv_datos].[servicio]))
GROUP BY srv_servicios.servicio, srv_servicios.id
ORDER BY srv_servicios.id
PIVOT srv_datos.municipio;

Como notarán en la consulta intervienen dos tablas, srv_servicios que tiene los campos id y servicios, este último es de tipo texto y contiene la descripción del servicio.

La tabla srv_datos contiene los campos id, servicio (este de tipo entero relacionado con srv_servicios.id), municipio, cantidad1, cantidad2, cantidad3.

La consulta anterior selecciona como encabezado de fila los diferentes servicios y como encabezado de columnas los diferentes municipios, en la intersección de filas y columnas la suma de las cantidades asociadas al servicio y municipio.

Necesito traducir esta consulta a MYSQL.

[leonardo_josue]

Hola pedrodiego:

Veo que este es tu primer post, así es que algunas cuantas recomendaciones para el futuro.

1. Pregunta a SAN GOOGLE. él es muy sabio y casi siempre tiene la respuesta... checa esta liga

http://lmgtfy.com/?q=transform+pivot+con+mysql


2. Si de pura casualidad SAN GOOGLE NO TIENE LA RESPUESTA utiliza la herramienta de búsqueda que ofrece el foro para ver si alguien más preguntó lo mismo (o algo parecido) de tal manera que no dupliques preguntas. (En la pagina principal del foro, debajo del botón de RELEVANCIA aparece la liga que dice BUSCAR EN ESTE FORO y también tienes búsqueda avanzada.

http://www.forosdelweb.com/search.php?f=86

3. Si después de hacer esto, sigues sin dar con la solución, entonces sí publica tu pregunta, pero toma en cuenta lo que muchas personas que no sabemos cómo trabaja ACCESS y por tanto no sabemos qué es lo que hace PIVOT ni TRANSFORM... por lo tanto tratar de encontrar una equivalencia en MySQL resulta más que imposible... además de la estructura de tus tablas, pon algunos datos de ejemplo... y con esos datos de ejemplo dinos qué es lo que esperas obtener de salida.

Y finalmente, cuando utilices algún asistente para generar consultas, al menos observa si puedes reducir la consulta...

Código SQL:

(((srv_servicios.id)=[srv_datos].[servicio]))
 

Todos los paréntesis SON COMPLETAMENTE INUTILES

Saludos
Leo.

[pedrodiego]

Saludos Leonardo, gracias por la respuesta y sobre todo tus recomendaciones, trataré en este caso de ser más explícito a la hora de plantear mi problema, primero la consulta SQL que vio fue generada como usted dice por un asistente, no la utilizo en lo que estoy desarrollando fue copiada y pegada a menara de ejemplo.

Tengo una tabla srv_servicios con los campos id, servicio

Contiene algo como esto

Id | servicio
1 | Cursos Largos Presenciales
2 | Cursos Semi-Presenciales
3 | Cursos Cortos

Y así sucesivamente hasta completar 31 tipos de servicios

Por otro lado está la tabla srv_datos con los campos id, servicio(está relacionado con el campo id de la tabla srv_servicios), municipio, am89, am910, am1011, am1112

Contiene algo como esto:

Id|servicio |municipio |am89|am910|am1011|am1112
1| 1 | 2 | 10 | 2 | 0 | 10
2| 2 | 1 | 5 | 4 | 10 | 0
3| 3 | 4 | 5 | 0 | 4 | 4
4| 1 | 1 | 0 | 2 | 0 | 3
5| 2 | 1 | 1 | 3 | 0 | 6

El resultado que quiero sería algo como esto

Servicio |municipio1 | municipio2| municipio3 | municipio4|
Cursos Largos Presenciales | 5 | 22 | 0 | 0 |
Cursos Semi-Presenciales | 29 | 0 | 0 | 0 |
Cursos Cortos | 0 | 0 | 0 | 13 |

Como verás los resultados son la suma de (am89…..am1112)

Creo que ahora me expliqué mejor, si pueden ayudarme estaré agradecido

[leonardo_josue]

Hola de nuevo pedrodiego:

Creo que no hiciste caso a los dos primeros puntos que te comente, acerca de preguntar a San GOOGLE y buscar en el foro , pero bueno, al menos hiciste caso en ampliar la explicación de lo que necesitas...

Este temo lo hemos tratado muchas veces, (por eso la insistencia en que buscaras información)... En MySQL lamentablemente no existe el concepto de referencias cruzadas, pero tú puedes simular este tipo de consultas haciendo agrupaciones condicionales o con algunos procedimientos automáticos... checa esta liga:

http://www.artfulsoftware.com/infotree/queries.php

hay dos temas que estoy seguro te pueden ayudar:

• Pivot table basics: rows to columns
• Automate pivot table queries

Si tus municipios no son muchos, entonces podrías optar por hacer un SUM condicional más o menos como el ejemplo:

Código:

SUM(CASE salesperson WHEN 'bob' THEN amount ELSE 0 END ) AS 'Bob',

o

SUM(IF(salesperson='bob', amount, 0))

En tu caso, el lugar de comparar el campo salespersons pondrías el campo MUNICIPIO, y en lugar del campo amount pondrías
(d.am89 + d.am910 + d.am1011 + d.am1112) donde d es tu tabla srv_datos

Si tus municipios son muchos, poner demasiadas sumas condicionales puede resultar bastante problemático... si es el caso entonces revisa el segundo tema, ahí te muestra forma en que podrías "automatizar" tu consulta para cualquier numero de municipios.

revisa la liga, si tienes problemas POSTEA ALGO DE LO QUE INTENTASTE HACER y con gusto te ayudamos a corregir la consulta.

Saludos.
Leo.

[pedrodiego]

Reitero las gracias Leonardo por tu preocupación, felizmente ya logré la consulta se la muestro

Código:

select     i.servicio servicio
,          sum(
               if(f.municipio=1
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Manati"
,          sum(
               if(f.municipio=2
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "pp"
,          sum(
               if(f.municipio=3
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Menendez"
,          sum(
               if(f.municipio=4
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Majibacoa"
,          sum(
               if(f.municipio=5
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Tunas"
,          sum(
               if(f.municipio=6
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Jobabo"
,          sum(
               if(f.municipio=7
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Colombia"
,          sum(
               if(f.municipio=8
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "Amancio"
,          sum(
               if(f.municipio=9
               ,   am89+am910+am1011+am1112
               ,   null
               )
           ) "CI"

from       srv_datos  f

inner join srv_servicios       i
on         f.servicio      = i.id

group by   f.servicio

Gracias por el link que me sugirió, en el encontré la respuesta.

[leonardo_josue]

Bien hecho, me da gusto que hayas dado con la respuesta ... Después de todo no fue tan difícil verdad???

La liga que te pasé, como su título lo dice tiene muchísimas consultas que se utilizan de manera cotidiana... te sugiero que le des un vistazo a detalle... estoy seguro que te encontrarás con cosas que te servirán en el futuro... así es que si fuera tú la pondría directamente en Favoritos.

Saludos
Leo.