MySQL Pivot Rotcing lines para colunas

Uma tabela de banco de dados pode armazenar diferentes tipos de dados e, às vezes, precisamos transformar dados no nível da linha em dados no nível da coluna. Este problema pode ser resolvido usando a função pivot (). Esta função é usada para girar linhas de uma tabela em valores de coluna. Mas essa função é suportada por muito poucos servidores de banco de dados, como Oracle ou SQL Server. Se você deseja executar a mesma tarefa na tabela de banco de dados MySQL, deve escrever a consulta selecionada usando a instrução CASE para girar as linhas para colunas. O artigo mostra a maneira de fazer a tarefa de função pivot () dentro das tabelas de banco de dados MySQL relacionadas.

Pré -requisito:

Você precisa criar um banco de dados e algumas tabelas relacionadas, onde as linhas de uma tabela serão convertidas nas colunas como a função pivot (). Execute as seguintes instruções SQL para criar um banco de dados chamado 'unidb'e crie três mesas nomeadas'alunos','cursos' e 'resultado'. alunos e resultado As mesas serão relacionadas por um relacionamento um para muitos e cursos e resultados As tabelas serão relacionadas por um relacionamento um para muitos aqui. Criar declaração do resultado A tabela contém duas restrições de chave estrangeira para os campos, std_id, e identidade do curso.

Criar banco de dados unidb;
Use unidb;
Crie alunos da tabela (
Id int chave primária,
Nome Varchar (50) não nulo,
Departamento Varchar (15) não nulo);
Crie cursos de tabela (
Course_Id Varchar (20) Chave primária,
Nome Varchar (50) não nulo,
crédito smallint não nulo);
Crie resultado da tabela (
std_id int não nulo,
Course_Id Varchar (20) Não NULL,
mark_type varchar (20) não nulo,
marca SmallInt não nulo,
Chave estrangeira (STD_ID) Referencia os alunos (ID),
Chave estrangeira (curso_ID) Referências cursos (curso_id),
Chave primária (std_id, curso_id, mark_type));

Insira alguns registros em alunos, cursos e resultado mesas. Os valores devem ser inseridos nas tabelas com base nas restrições definidas no momento da criação da tabela.

Insira os valores dos alunos
('1937463', 'Harper Lee', 'CSE'),
('1937464', 'Garcia Marquez', 'CSE'),
('1937465', 'Forster, e.M.',' CSE '),
('1937466', 'Ralph Ellison', 'CSE');
Insira os valores dos cursos
('CSE-401', 'Programação orientada a objeto', 3),
('CSE-403', 'Estrutura de dados', 2),
('CSE-407', 'UNIX Programming', 2);
Insira nos valores dos resultados
('1937463', 'CSE-401', 'Exame interno', 15),
('1937463', 'CSE-401', 'Exame de médio prazo', 20),
('1937463', 'CSE-401', 'Exame Final', 35),
('1937464', 'CSE-403', 'Exame interno', 17),
('1937464', 'CSE-403', 'Exame de médio prazo', 15),
('1937464', 'CSE-403', 'Exame Final', 30),
('1937465', 'CSE-401', 'Exame interno', 18),
('1937465', 'CSE-401', 'Exame de médio prazo', 23),
('1937465', 'CSE-401', 'Exame Final', 38),
('1937466', 'CSE-407', 'Exame interno', 20),
('1937466', 'CSE-407', 'Exame de médio prazo', 22),
('1937466', 'CSE-407', 'Exame Final', 40);

Aqui, resultado A tabela contém vários mesmos valores para std_id, mark_type e identidade do curso colunas em cada linha. Como converter essas linhas em colunas desta tabela para exibir os dados em um formato mais organizado é mostrado na próxima parte deste tutorial.

Gire as linhas para colunas usando a instrução CASE:

Execute a seguinte instrução Select simples para exibir todos os registros do resultado mesa.

Selecione * do resultado;

A saída mostra as quatro marcas de estudantes para três tipos de exames de três cursos. Então os valores de std_id, identidade do curso e mark_type são repetidos várias vezes para os diferentes alunos, cursos e tipos de exames.

A saída será mais legível se a consulta selecionada puder ser escrita com mais eficiência usando a instrução CASE. O seguinte selecionado com a instrução CASE transformará os valores repetidos das linhas nos nomes das colunas e exibirá o conteúdo das tabelas em um formato mais compreensível para o usuário.

Selecione o resultado.std_id, resultado.identidade do curso,
Max (caso quando resultado.mark_type = "Exame interno" e depois resultado.marcas terminam) "Exame interno",
Max (caso quando resultado.mark_type = "Exame médio" e depois resultado.marcas terminam) "Exame de médio prazo",
Max (caso quando resultado.Mark_type = "Exame Final" e depois resultado.marcas terminam) "Exame Final"
Do resultado
Grupo por resultado.std_id, resultado.identidade do curso
Ordem por resultado.std_id, resultado.curso_ID ASC;

A saída a seguir será exibida após a execução da declaração acima que é mais legível que a saída anterior.

Gire as linhas para colunas usando case e sum ():

Se você deseja contar o número total de cada curso de cada aluno da tabela, você deve usar a função agregada SOMA() grupo por std_id e identidade do curso com a declaração do caso. A consulta a seguir é criada modificando a consulta anterior com SUM () Função e Grupo por Cláusula.

Selecione o resultado.std_id, resultado.identidade do curso,
Max (caso quando resultado.mark_type = "Exame interno" e depois resultado.marcas terminam) "Exame interno",
Max (caso quando resultado.mark_type = "Exame médio" e depois resultado.marcas terminam) "Exame de médio prazo",
Max (caso quando resultado.Mark_type = "Exame Final" e depois resultado.marcas terminam) "Exame Final",
Soma (resultado.marcas) como total
Do resultado
Grupo por resultado.std_id, resultado.identidade do curso
Ordem por resultado.std_id, resultado.curso_ID ASC;

A saída mostra uma nova coluna chamada Total Isso está exibindo a soma das marcas de todos os tipos de exames de cada curso obtido por cada aluno em particular.

Gire as linhas para colunas em várias tabelas:

As duas consultas anteriores são aplicadas ao resultado mesa. Esta tabela está relacionada às outras duas tabelas. Estes são alunos e cursos. Se você deseja exibir o nome do aluno em vez de identificação do aluno e nome do curso em vez de ID do curso, você deve escrever a consulta selecionada usando três tabelas relacionadas, alunos, cursos e resultado. A consulta selecionada a seguir é criada adicionando três nomes de tabela após a cláusula de formulário e definindo condições apropriadas na cláusula WHERE para recuperar os dados das três tabelas e gerar saída mais apropriada do que as consultas selecionadas anteriores.

Selecione alunos.nome como 'nome do aluno', cursos.nome como 'nome do curso',
Max (caso quando resultado.mark_type = "Exame interno" e depois resultado.marcas terminam) "CT",
Max (caso quando resultado.mark_type = "Exame médio" e depois resultado.marcas terminam) "Mid",
Max (caso quando resultado.Mark_type = "Exame Final" e depois resultado.marcas terminam) "final",
Soma (resultado.marcas) como total
De estudantes, cursos, resultado
Onde resultado.std_id = alunos.id e resultado.curso_id = cursos.identidade do curso
Grupo por resultado.std_id, resultado.identidade do curso
Ordem por resultado.std_id, resultado.curso_ID ASC;

A saída a seguir gerará após a execução da consulta acima.

Conclusão:

Como você pode implementar a funcionalidade da função pivot () sem o suporte da função pivot () no mysql é mostrado neste artigo usando alguns dados fictícios. Espero que os leitores possam transformar qualquer dados no nível da linha em dados no nível da coluna usando a consulta Select após ler este artigo.