HOWTO · MySQL

Tabla dinámica en MySQL

Este artículo muestra diferentes formas de generar tablas dinámicas en MySQL.

En esta página

Pivotar convierte filas en columnas, a menudo para crear un resumen o un informe. MySQL no incluye una función integrada PIVOT, pero la agregación condicional permite crear columnas fijas con IF o CASE. Cuando cambia el conjunto de columnas de salida, el SQL dinámico puede generar la consulta a partir de los datos. El ejemplo usa las calificaciones de estudiantes: al agrupar por estudiante se obtiene una fila por nombre y cada asignatura se convierte en una columna.

Preparar los datos de ejemplo

Los datos de ejemplo contienen dos calificaciones de Sam en la asignatura 1. La tabla dinámica esperada usa la calificación más alta: 99. Martin no tiene ninguna fila para la asignatura 4, por lo que los ejemplos con columnas fijas muestran NULL en esa columna. La imagen de entrada proporcionada muestra los mismos registros que se usan a continuación.

Tabla de entrada de estudiantes con las columnas de nombre, identificador de asignatura y calificaciones

Los registros de entrada incluyen dos calificaciones de Sam en la asignatura 1, así que la tabla dinámica debe mostrar el valor más alto: 99.

Ejecute este ejemplo:

El código y las etiquetas o salidas visibles de esta sección se mantienen en inglés.

rows = [
    ("Sam", 1, 70), ("Sam", 2, 77), ("Sam", 3, 71), ("Sam", 4, 70),
    ("Sam", 1, 99), ("John", 1, 89), ("John", 2, 87),
    ("John", 3, 88), ("John", 4, 89), ("Martin", 1, 60),
    ("Martin", 2, 47), ("Martin", 3, 68),
]
subjects = (1, 2, 3, 4)
print("name|" + "|".join(f"Sub{s}" for s in subjects))
for name in sorted({row[0] for row in rows}):
    marks = [max((score for student, subject, score in rows
                  if student == name and subject == s), default="NULL")
             for s in subjects]
    print(name + "|" + "|".join(str(score) for score in marks))
name|Sub1|Sub2|Sub3|Sub4
John|89|87|88|89
Martin|60|47|68|NULL
Sam|99|77|71|70

Esta ilustración en Python permite ver cómo funcionan la agrupación y la selección de la calificación máxima; use las consultas de MySQL siguientes con su tabla. Las etiquetas y los resultados visibles de la ilustración permanecen en inglés. La instrucción SQL para crear la tabla es CREATE TABLE student (name VARCHAR(20), subjectid INT, marks INT);. Añada las filas que aparecen en la imagen, incluidos los dos registros de Sam para la asignatura 1, y luego use la agregación condicional para generar el informe.

Pivotar con IF y MAX

Escriba una expresión condicional para cada columna de asignatura y luego agrupe por name para obtener una fila por estudiante: SELECT name, MAX(IF(subjectid=1, marks, NULL)) AS Sub1, MAX(IF(subjectid=2, marks, NULL)) AS Sub2, MAX(IF(subjectid=3, marks, NULL)) AS Sub3, MAX(IF(subjectid=4, marks, NULL)) AS Sub4 FROM student GROUP BY name;. IF devuelve la calificación de una fila que cumple la condición y NULL en caso contrario. MAX agrega esos valores dentro de cada nombre y cada columna de asignatura. Si hay filas duplicadas para el mismo estudiante y la misma asignatura, MAX devuelve la calificación coincidente más alta; no selecciona la primera ni limita la cantidad de filas.

Resultado de tabla dinámica con columnas fijas mediante agregación condicional

El resultado con columnas fijas muestra la calificación más alta de Sam en la asignatura 1, 99, y NULL cuando Martin no tiene un registro para la asignatura 4.

La imagen muestra el resultado de la consulta: incluye el valor 99 de Sam para la asignatura 1 y NULL para la asignatura 4 que le falta a Martin. Son imágenes de resultados, no capturas de un cliente de bases de datos. El SQL y el resultado se pueden seleccionar aquí.

Pivotar con CASE

CASE expresa la misma condición de forma explícita. Esta consulta produce el mismo resultado con columnas fijas que la versión con IF: SELECT name, MAX(CASE WHEN subjectid=1 THEN marks END) AS Sub1, MAX(CASE WHEN subjectid=2 THEN marks END) AS Sub2, MAX(CASE WHEN subjectid=3 THEN marks END) AS Sub3, MAX(CASE WHEN subjectid=4 THEN marks END) AS Sub4 FROM student GROUP BY name;. Sin una cláusula ELSE, una fila que no cumple la condición produce NULL; MAX ignora esos valores. Por eso, si un estudiante no tiene ninguna fila coincidente para una asignatura, el resultado de esa columna es NULL. Los duplicados se procesan con la misma regla del valor máximo.

Generar columnas dinámicas para la tabla dinámica

Las consultas con IF y CASE tienen columnas de salida fijas: al escribir la consulta, debe conocer los valores de las asignaturas y añadir una expresión para cada columna. El SQL dinámico puede generar esas expresiones a partir de los identificadores de asignatura distintos. GROUP_CONCAT construye las expresiones de las columnas, y una instrucción preparada ejecuta la consulta resultante:

SET SESSION group_concat_max_len = 100000; SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) AS \’, subjectid, ‘`’) ORDER BY subjectid SEPARATOR ‘, ‘) INTO @columns FROM student; SET @sql = CONCAT(‘SELECT name, ‘, @columns, ’ FROM student GROUP BY name’); SELECT @sql; PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;`

La consulta generada incluye una columna de salida por cada asignatura presente en la tabla. En este ejemplo, las calificaciones no son negativas, así que ELSE 0 hace que una asignatura sin calificación muestre 0, como en la imagen del resultado dinámico. Esta opción solo es correcta cuando las calificaciones no pueden ser negativas: de lo contrario, el 0 de una fila que no coincide podría superar una calificación negativa coincidente al aplicar MAX. Si las calificaciones pueden ser negativas, use ELSE NULL; así, MAX ignora las filas que no coinciden y una columna sin ninguna fila coincidente para un estudiante devuelve NULL. Revise @sql antes de prepararlo, sobre todo si los valores o identificadores pueden proceder de entradas no confiables. La instrucción generada y la instrucción preparada tienen alcance de sesión, así que ejecute estas instrucciones en la misma conexión de MySQL. GROUP_CONCAT tiene una longitud máxima configurable; si la lista de expresiones generada supera group_concat_max_len, puede quedar truncada. Establezca un valor de sesión adecuado e inspeccione el SQL generado antes de ejecutarlo.

Resultado dinámico de tabla dinámica con columnas basadas en las asignaturas

El resultado generado añade una columna por cada identificador de asignatura; dado que las calificaciones del ejemplo no son negativas, la asignatura 4 que le falta a Martin muestra 0.

Este enfoque adapta la lista de columnas a los identificadores de asignatura de los datos. Aun así, la forma del resultado queda determinada al preparar la instrucción, y puede ser más fácil procesar un conjunto de asignaturas muy grande o cambiante en el código de la aplicación o en una herramienta de informes.