SELECT con campos columnados

[adrall]

Es posible hacer una consulta SELECT sobre MySQL que devuelva diversas columnas según el valor de un campo?

Imaginemos que este SELECT


SELECT cliente, mes, sum(cantidad) AS importe FROM compra GROUP BY mes,cliente;


devuelve esta muestra

+----------+--------+-----------+
| cliente | mes | importe |
+----------+--------+-----------+
| PEPE | 201901 | 25000 |
| ROSA | 201901 | 180000 |
| EDU | 201901 | 128000 |
| JAIME | 201901 | 20000 |
| JUAN | 201902 | 250000 |
| PEPE | 201902 | 25000 |
| ROSA | 201902 | 180000 |
| EDU | 201902 | 128000 |
| JAIME | 201902 | 20000 |
| JUAN | 201902 | 250000 |
| PEPE | 201903 | 25000 |
| ROSA | 201903 | 180000 |
| EDU | 201903 | 128000 |
| JAIME | 201903 | 20000 |
| JUAN | 201904 | 250000 |
| PEPE | 201904 | 25000 |
| ROSA | 201904 | 180000 |
| EDU | 201904 | 128000 |
| JAIME | 201904 | 20000 |
| JUAN | 201904 | 250000 |
+----------+--------+-----------+

Puedo obtener las cantidades de cada mes por columnas agrupado por clientes de alguna manera, esto es posible ??

Muchas gracias

[mamcx]

No se entiende. Da el ejemplo de como deberia (http://www.clubdelphi.com/foros/showthread.php?t=93348) quedar.

[oscarac]

busca la utilizacion de "Pivot"

[adrall]

Deberia quedar asi:

+----------+--------+-----------++----------+--------+-----------++----------+--------+-----------++----------+--------+-----------+
| cliente | enero | febrero | marzo | abril | mayo | abril etc. etc.
+----------+--------+-----------++----------+--------+-----------++----------+--------+-----------++----------+--------+-----------+
| PEPE | 5000 | 300 |
| ROSA | 8000 | 0 |
| EDU | 0 | 1230 | etc. etc.
| JAIME | 200 | 0 |
| JUAN | 50000 | 300 |
+----------+--------+-----------++----------+--------+-----------++----------+--------+-----------++----------+--------+-----------+

en cada columna / mes las cantidades que correspondan.

Gracias

[adrall]

busca la utilizacion de "Pivot"

Investigaré las tablas Pivot, desconocía esta funcionalidad.

Muchas gracias. y saludos

[bucanero]

hola a todos

Una forma de poder solucionar tu problema puede ser esta:

select distinct cliente.cliente,
Ifnull((
-- ventas enero
SELECT sum(importe) AS importe
FROM compra
WHERE month(fecha) = 1 and year(fecha) = year(now()) AND cliente=cliente.cliente
GROUP BY cliente, month(fecha)
), 0) as enero,
Ifnull((
-- ventas enero
SELECT sum(importe) AS importe
FROM compra
WHERE month(fecha) = 2 and year(fecha) = year(now()) AND cliente=cliente.cliente
GROUP BY cliente, month(fecha)
), 0) as febrero,
-- ...,
Ifnull((
-- ventas enero
SELECT sum(importe) AS importe
FROM compra
WHERE month(fecha) = 12 and year(fecha) = year(now()) AND cliente=cliente.cliente
GROUP BY cliente, month(fecha)
), 0) as diciembre
from compra cliente
WHERE year(fecha) = year(now()) AND cliente<>''
GROUP BY cliente


El problema:
que te sale una morcilla de SQL... y a parte puede llegar a ser bastante lenta la ejecución, y cualquier cambio en las subconsultas de los meses tienes que aplicarlas a los doce meses.

busca la utilizacion de "Pivot"
En cuanto a "PIVOT", que es la solución perfecta a este problema, es un método que realiza el motor de BBDD de microsoft MsSQL y no se si existe también en algún otro motor de BBDD, pero creo que no existe como tal en MySQL, o por lo menos yo no he encontrado nunca información de como aplicarlo directamente para MySQL. Si es verdad que con procedimientos y tablas en memoria se puede llegar a hacer, pero no es tan trivial como lo hace MsSQL.

Lo que si tiene MySQL y se puede utilizar en este caso es la función GROUP_CONCAT() que puede hacer algo similar, concatena los valores de un determinado campo para varios registros cuando se hace un GROUP BY

El ejemplo para este caso sería este:

SELECT cliente.cliente, GROUP_CONCAT(ifNull(ventas.importe, 0) ORDER BY mes.mes SEPARATOR '\,') as resumen
FROM
-- genera un registro para cada mes de cada cliente
((SELECT DISTINCT month(fecha) AS mes FROM compra) mes,
(SELECT DISTINCT cliente FROM compra) cliente)
-- rellena los importes del mes
LEFT JOIN (
SELECT cliente, month(fecha) AS mes, sum(importe) AS importe
FROM compra
WHERE year(fecha)=year(now()) AND cliente<>''
GROUP BY cliente, month(fecha)
) ventas ON ventas.cliente=cliente.cliente AND ventas.mes=mes.mes
GROUP BY cliente


Ventajas: El SQL es mucho mas corto que el anterior, y a nivel de rendimiento, también es mas rápido.
Desventajas: Los datos de ventas salen todos en el mismo campo, separados por comas. Pero entre comillas es un mal menor, puesto que ya desde delphi es relativamente fácil procesarlos, por ejemplo añadiendo los campos de los meses como campos calculados en el query/tabla e inserta el valor en evento onCalculate

[bucanero]

Dandole vueltas al tema de extraer la ventas de cada mes del campo con la lista de valores separados por comas, se puede utilizar algo así:

select cliente,
cast(substring_index(substring_index(resumen, ',', 1), ',', -1) AS DECIMAL(10,2)) as enero,
cast(substring_index(substring_index(resumen, ',', 2), ',', -1) AS DECIMAL(10,2)) as febrero,
cast(substring_index(substring_index(resumen, ',', 3), ',', -1) AS DECIMAL(10,2)) as marzo,
cast(substring_index(substring_index(resumen, ',', 4), ',', -1) AS DECIMAL(10,2)) as abril,
cast(substring_index(substring_index(resumen, ',', 5), ',', -1) AS DECIMAL(10,2)) as mayo,
cast(substring_index(substring_index(resumen, ',', 6), ',', -1) AS DECIMAL(10,2)) as junio,
cast(substring_index(substring_index(resumen, ',', 7), ',', -1) AS DECIMAL(10,2)) as julio,
cast(substring_index(substring_index(resumen, ',', 8), ',', -1) AS DECIMAL(10,2)) as agosto,
cast(substring_index(substring_index(resumen, ',', 9), ',', -1) AS DECIMAL(10,2)) as septiembre,
cast(substring_index(substring_index(resumen, ',', 10), ',', -1) AS DECIMAL(10,2)) as octubre,
cast(substring_index(substring_index(resumen, ',', 11), ',', -1) AS DECIMAL(10,2)) as noviembre,
cast(substring_index(substring_index(resumen, ',', 12), ',', -1) AS DECIMAL(10,2)) as diciembre
from (
-- mismo SQL anterior
SELECT cliente.cliente, GROUP_CONCAT(ifNull(ventas.importe, 0) ORDER BY mes.mes SEPARATOR '\,') as resumen
FROM
-- genera un registro para cada mes de cada cliente
((SELECT DISTINCT month(fecha) AS mes FROM compra) mes,
(SELECT DISTINCT cliente FROM compra) cliente)
-- rellena los importes del mes
LEFT JOIN (
SELECT cliente, month(fecha) AS mes, sum(importe) AS importe
FROM compra
WHERE year(fecha)=year(now()) AND cliente<>''
GROUP BY cliente, month(fecha)
) ventas ON ventas.cliente=cliente.cliente AND ventas.mes=mes.mes
GROUP BY cliente
--
) dat

y con esto ya si se tiene una tabla con cada venta de un determinado mes en su correspondiente columna del mes

[orodriguezca]

Yo probaría la siguiente instrucción:


SELECT
cliente,
sum(case when mes = 201901 then Cantidad else 0) as Enero,
sum(case when mes = 201902 then Cantidad else 0) as Febrero,
sum(case when mes = 201903 then Cantidad else 0) as Marzo,
sum(case when mes = 201904 then Cantidad else 0) as Abril,
sum(case when mes = 201905 then Cantidad else 0) as Mayo,
sum(case when mes = 201906 then Cantidad else 0) as Junio,
sum(case when mes = 201907 then Cantidad else 0) as Julio,
sum(case when mes = 201908 then Cantidad else 0) as Agosto,
sum(case when mes = 201909 then Cantidad else 0) as Septiembre,
sum(case when mes = 201910 then Cantidad else 0) as Octubre,
sum(case when mes = 201911 then Cantidad else 0) as Noviembre,
sum(case when mes = 201912 then Cantidad else 0) as Diciembre,
FROM compra
GROUP BY cliente, mes;

[adrall]

Si esta es la solución que me ha parecido mejor aunque he utilizado if en lugar de case .

También he visto alguna solución para cuando no se conocen previamente los nombre de los campos, como esta:

http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

Muchas gracias y saludos a todos.