Tabla dinámica en MySQL

1. Cree una tabla dinámica en MySQL usando la función MAX y la instrucción IF
2. Cree una tabla dinámica en MySQL usando una declaración CASE
3. Crear tabla dinámica en MySQL usando columnas dinámicas dinámicas

En este artículo, describimos cómo transformar los datos de una tabla de filas a columnas. Este proceso se llama pivotar. El resultado de esta transformación suele ser una tabla resumen donde presentamos información apta para la generación de informes.

En MySQL, no hay una función incorporada para crear tablas dinámicas, por lo que tendrá que escribir una consulta MySQL para generar una tabla dinámica. Afortunadamente, hay tres formas diferentes de crear una tabla dinámica usando MySQL.

1. Cree una tabla dinámica en MySQL usando la declaración IF
2. Cree una tabla dinámica en MySQL usando una declaración CASE
3. Cree una tabla dinámica en MySQL utilizando columnas dinámicas dinámicas

El siguiente guión crea una tabla de alumnos con tres columnas (name, subjectid y marks).

create table student(name varchar(20),subjectid int(10), marks int(10));

Aquí, insertamos valores de datos de muestra en la tabla de estudiantes para la demostración.

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

Después de insertar estos valores, escribamos una consulta seleccionar para ver los registros en la base de datos.

Select * from student;

La salida será:

Cree una tabla dinámica en MySQL usando la función MAX y la instrucción IF

Este es un enfoque muy sencillo del problema. En este enfoque, usaremos la función MAX y la instrucción IF. Si hay varias entradas para una sola materia para un estudiante en la base de datos, se necesitarán dos puntos como máximo.

Veamos ahora cómo usar esto con la consulta SELECT:

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;

La salida será:

Esta salida es un resultado compilado para cada estudiante por materia. Dado que desea una fila para cada alumno, debe agrupar por la columna de nombre. Además, debe especificar una condición para cada columna, es decir, una condición por tema.

Cree una tabla dinámica en MySQL usando una declaración CASE

Este también es un enfoque sencillo del problema. Usaremos la función MAX junto con la declaración CASE en este enfoque. Además, si hay varias entradas para una sola materia para un estudiante en la base de datos, se necesitarán dos puntos como máximo. De manera similar, si no existen calificaciones para algunos estudiantes, tomará el valor NULO.

Veamos ahora cómo usar esto con la consulta SELECT:

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

La salida será:

Crear tabla dinámica en MySQL usando columnas dinámicas dinámicas

Una función agregada (MAX), una sentencia IF y una sentencia CASE generaron la tabla dinámica en el ejemplo anterior. El inconveniente de usar ese enfoque es que necesitamos conocer los encabezados de las columnas mientras escribimos la consulta, y cuando aumenta el número de columnas, también lo hace el código. Podemos optar por los dos enfoques anteriores para obtener resultados más pequeños y todos los valores posibles.

Entonces, para superar estas limitaciones, podemos usar columnas dinámicas de pivote. Aquí, la función GROUP_CONCAT puede generar dinámicamente las columnas de una salida de tabla PIVOT.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, 
 ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

En una función GROUP_CONCAT, usamos la palabra clave DISTINCT para obtener una lista única de marcas. De lo contrario, nuestra consulta daría un error porque la lista es demasiado larga. Puede escribir Select * from @sql para comprobar la consulta dinámica en cualquier momento.

Ahora, cuando ejecutamos esta consulta, esto es lo que obtenemos como resultado:

Tenga en cuenta que los encabezados de columna se generan dinámicamente en función de los valores de la tabla y que el encabezado de columna representa el subjectid.