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 😉