Cómo armar una tabla pivot con PHP y MySQL

Hurgando en las profundidades de la Internet me encontré con una pregunta muy interesante.

El autor comentaba que tenía una tabla con esta pinta:

Y quería, mediante una consulta a MySQL, obtener un resultado de este tipo:

Básicamente el desafío era transformar datos horizontales (los valores de la columna «endpoint» para cada fila) en verticales, es decir, columnas de la respuesta.

No es algo que se vea todos los días, cierto, pero… ¿cómo rechazar un desafío semejante? 🙂

Inmediatamente me vino a la mente el concepto de Tabla Pivot que manejan las planillas de cálculo.

Así que me arremangué y escribí este SQL:

SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60) as fecha_hora,
    SUM(
		CASE
		  WHEN endpoint = 50
		   THEN numero_personas
		   ELSE 0
		END
    ) as suma_personas_ep50,
    SUM(
		CASE
			WHEN endpoint = 51
			THEN numero_personas
			ELSE 0
		END
    ) as suma_personas_ep51
	FROM t
	GROUP BY FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60)
	;

Con esto obtuve el resultado esperado. ¡Exito!

Pero… inmediatamente surgió la pregunta en mi cabeza: ¿Qué pasará cuando se agreguen nuevos endpoints?

Pues… ¡usemos un poco de PHP!

<?php

try {
	$pdo = new PDO("mysql:dbname=escuelait;host=localhost", "root", "1234");
} catch ( PDOException $e ) {
	die ( $e->getMessage() );
}

$sql = "SELECT DISTINCT(endpoint) FROM t;";

$endPoints = $pdo->query($sql, PDO::FETCH_COLUMN,0)->fetchAll();

$templateSQL = "SUM(
		CASE
		  WHEN endpoint = |ENDPOINT|
		   THEN numero_personas
		   ELSE 0
		END
    ) as suma_personas_ep|ENDPOINT|";
	
$endPointsSQL = implode( ",".PHP_EOL, array_map( function( $endPoint ) use ( $templateSQL ) {
	
	return preg_replace( '/\|ENDPOINT\|/', $endPoint, $templateSQL ) ;
}, $endPoints ) );

$finalSQL = "SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60) as fecha_hora,
    $endPointsSQL
	FROM t
	GROUP BY FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)*60)
	;";
	
echo $finalSQL.PHP_EOL;

Este script realiza dos consultas:

  1. La primera para obtener los endpoints existentes
  2. La segunda para generar la tabla dinámica

El truco está en generar el string SQL en forma dinámica.

No es la sintaxis más utilizada pero… puede serte útil alguna vez 😉

mchojrin

Por mchojrin

Ayudo a desarrolladores PHP a acceder mercados y clientes más sofisticados y exigentes

4 comentarios

  1. Hola, excelente muchas gracias ya adecue la consulta para obtener ventas de productos de diferentes sucursales, estos reportes se pueden obtener por bimestre, semana, mensual, por sucursal, etc etc…

  2. Excelente, generar las consultas SQL de manera dinamica , ejecutarlas y llevarlas a arreglos da mucha flexibilidad para armar las tablas pivote como uno quiera , muchas gracias!!!!

  3. Hola al ejecutar la consulta muestra este resultado.

    SELECT FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)60) as fecha_hora, , FROM t GROUP BY FROM_UNIXTIME(FLOOR((UNIX_TIMESTAMP(fecha_hora))/60)60) ;

¿Te quedó alguna duda? Publica aca tu pregunta

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.