Mysql pivote filas rotativas a columnas

Una tabla de bases de datos puede almacenar diferentes tipos de datos y, a veces, necesitamos transformar datos de nivel de fila en datos a nivel de columna. Este problema se puede resolver usando la función Pivot (). Esta función se usa para rotar filas de una tabla en valores de columna. Pero esta función es compatible con muy pocos servidores de bases de datos, como Oracle o SQL Server. Si desea hacer la misma tarea en la tabla de la base de datos MySQL, debe escribir la consulta de selección utilizando la instrucción Case para rotar las filas en columnas. El artículo muestra la forma de hacer la tarea de la función Pivot () dentro de las tablas de base de datos MySQL relacionadas.

Requisito previo:

Debe crear una base de datos y algunas tablas relacionadas donde las filas de una tabla se convertirán en columnas como la función Pivot (). Ejecute las siguientes declaraciones SQL para crear una base de datos llamada 'unidb'y crea tres tablas nombradas'estudiantes','cursos' y 'resultado'. estudiantes y resultado Las tablas estarán relacionadas con una relación de uno a muchos y cursos y resultados Las tablas estarán relacionadas con una relación de uno a muchos aquí. Crear una declaración de la resultado La tabla contiene dos restricciones de clave extranjera para los campos, std_id, y curso_id.

Crear base de datos UNIDB;
Usar unidb;
Crear estudiantes de mesa (
ID int Prime Key,
Nombre Varchar (50) No nulo,
Departamento Varchar (15) no nulo);
Crear cursos de mesa (
curso_id varchar (20) Clave principal,
Nombre Varchar (50) No nulo,
crédito pequeño no nulo);
Crear resultado de la tabla (
std_id int no nulo,
curso_id varchar (20) No nulo,
mark_type varchar (20) no nulo,
Marca Smallint no nula,
REFERENCIA DE LA CLAVE EXTRANJERO (STD_ID) REFERENCIAS ESTUDIANTES (ID),
Cursos de referencias de la clave extranjera (curso_id) (curso_id),
Clave primaria (std_id, curso_id, mark_type));

Inserte algunos registros en estudiantes, cursos y resultados mesas. Los valores deben insertarse en las tablas en función de las restricciones establecidas en el momento de la creación de la tabla.

Insertar en los valores de los estudiantes
('1937463', 'Harper Lee', 'CSE'),
('1937464', 'García Márquez', 'CSE'),
('1937465', 'Forster, E.METRO.',' CSE '),
('1937466', 'Ralph Ellison', 'CSE');
Insertar en los valores de los cursos
('CSE-401', 'Programación orientada a objetos', 3),
('CSE-403', 'Estructura de datos', 2),
('CSE-407', 'Programación UNIX', 2);
Insertar en valores de resultados
('1937463', 'CSE-401', 'Examen interno', 15),
('1937463', 'CSE-401', 'Examen de mediano plazo', 20),
('1937463', 'CSE-401', 'Examen final', 35),
('1937464', 'CSE-403', 'Examen interno', 17),
('1937464', 'CSE-403', 'Examen de mediano plazo', 15),
('1937464', 'CSE-403', 'Examen final', 30),
('1937465', 'CSE-401', 'Examen interno', 18),
('1937465', 'CSE-401', 'Examen de mediano plazo', 23),
('1937465', 'CSE-401', 'Examen final', 38),
('1937466', 'CSE-407', 'Examen interno', 20),
('1937466', 'CSE-407', 'Examen de mediano plazo', 22),
('1937466', 'CSE-407', 'Examen final', 40);

Aquí, resultado La tabla contiene múltiples valores mismos para std_id, mark_type y curso_id columnas en cada fila. Cómo convertir estas filas en columnas de esta tabla para mostrar los datos en un formato más organizado se muestra en la siguiente parte de este tutorial.

Gire las filas a las columnas usando la instrucción Case:

Ejecute la siguiente declaración de selección simple para mostrar todos los registros del resultado mesa.

Seleccionar * de resultado;

La salida muestra las marcas de los cuatro estudiantes para tres tipos de examen de tres cursos. Entonces los valores de std_id, curso_id y mark_type se repiten varias veces para los diferentes estudiantes, cursos y tipos de exámenes.

La salida será más legible si la consulta SELECT se puede escribir de manera más eficiente utilizando la instrucción Case. La siguiente selección con la instrucción Case transformará los valores de repetición de las filas en los nombres de la columna y mostrará el contenido de las tablas en un formato más comprensible para el usuario.

Seleccionar resultado.std_id, resultado.curso_id,
Max (caso cuando el resultado.mark_type = "examen interno" y luego resultado.Marks End) "Examen interno",
Max (caso cuando el resultado.mark_type = "examen de mediano término" luego resultado.Marks End) "Examen de mitad de período",
Max (caso cuando el resultado.mark_type = "examen final" luego resultado.Marcas fin) "Examen final"
Desde el resultado
Grupo por resultado.std_id, resultado.curso_id
Orden por resultado.std_id, resultado.curso_id asc;

La siguiente salida aparecerá después de ejecutar la instrucción anterior que es más legible que la salida anterior.

Gire las filas a columnas usando Case y Sum ()::

Si desea contar el número total de cada curso de cada estudiante de la tabla, entonces debe usar la función agregada SUMA() agrupar por std_id y curso_id con la declaración de caso. La siguiente consulta se crea modificando la consulta anterior con la función y el grupo de suma () por cláusula.

Seleccionar resultado.std_id, resultado.curso_id,
Max (caso cuando el resultado.mark_type = "examen interno" y luego resultado.Marks End) "Examen interno",
Max (caso cuando el resultado.mark_type = "examen de mediano término" luego resultado.Marks End) "Examen de mitad de período",
Max (caso cuando el resultado.mark_type = "examen final" luego resultado.Marcas fin) "Examen final",
Suma (resultado.marcas) como total
Desde el resultado
Grupo por resultado.std_id, resultado.curso_id
Orden por resultado.std_id, resultado.curso_id asc;

La salida muestra una nueva columna nombrada Total Eso está mostrando la suma de las marcas de todos los tipos de examen de cada curso obtenido por cada estudiante en particular.

Gire las filas a columnas en múltiples tablas:

Las dos consultas anteriores se aplican al resultado mesa. Esta tabla está relacionada con las otras dos tablas. Estos son estudiantes y cursos. Si desea mostrar el nombre del estudiante en lugar de la identificación del estudiante y el nombre del curso en lugar de, por supuesto, la identificación, debe escribir la consulta de selección utilizando tres tablas relacionadas, estudiantes, cursos y resultado. La siguiente consulta de selección se crea agregando tres nombres de la tabla después de la cláusula de formulario y estableciendo condiciones apropiadas en la cláusula WHERE para recuperar los datos de las tres tablas y generar una salida más apropiada que las consultas de selección anteriores.

Seleccionar estudiantes.Nombre como 'Nombre del estudiante', cursos.nombre como 'nombre del curso',
Max (caso cuando el resultado.mark_type = "examen interno" y luego resultado.Marks End) "CT",
Max (caso cuando el resultado.mark_type = "examen de mediano término" luego resultado.Marcas fin) "Mid",
Max (caso cuando el resultado.mark_type = "examen final" luego resultado.las marcas finales) "final",
Suma (resultado.marcas) como total
De estudiantes, cursos, resultado
Donde resultado.std_id = estudiantes.ID y resultado.curso_id = cursos.curso_id
Grupo por resultado.std_id, resultado.curso_id
Orden por resultado.std_id, resultado.curso_id asc;

La siguiente salida generará después de ejecutar la consulta anterior.

Conclusión:

En este artículo se muestra cómo puede implementar la funcionalidad de la función Pivot () sin el soporte de la función Pivot () en MySQL utilizando algunos datos ficticios. Espero que los lectores puedan transformar cualquier datos de nivel de fila en datos a nivel de columna utilizando la consulta de selección después de leer este artículo.