Pré-requisito:
Você tem que criar um banco de dados e algumas tabelas relacionadas onde as linhas de uma tabela serão convertidas em colunas como a função PIVOT (). Execute as seguintes instruções SQL para criar um banco de dados chamado ‘ unidb ’E crie três tabelas chamadas‘ alunos ’,‘ cursos ' e ' resultado '. alunos e resultado as tabelas serão relacionadas por relacionamento um-para-muitos e cursos e resultados as tabelas serão relacionadas por relacionamento um-para-muitos aqui. Declaração CREATE do resultado tabela contém duas restrições de chave estrangeira para os campos, std_id , e identidade do curso .
CREATE DATABASE unidb;
USE unidb;
CRIAR TABELA alunos(
Eu iriaINT PRIMARY KEY,
nome varchar(cinquenta)NÃO NULO,
departamento VARCHAR(quinze)NÃO NULO);
CRIAR TABELA cursos(
course_id VARCHAR(vinte)CHAVE PRIMÁRIA,
nome varchar(cinquenta)NÃO NULO,
crédito SMALLINT NOT NULL);
Resultado de CREATE TABLE(
std_id INT NOT NULL,
course_id VARCHAR(vinte)NÃO NULO,
mark_type VARCHAR(vinte)NÃO NULO,
marca SMALLINT NOT NULL,
CHAVE ESTRANGEIRA(std_id)REFERÊNCIAS alunos(Eu iria),
CHAVE ESTRANGEIRA(identidade do curso)REFERÊNCIAS cursos(identidade do curso),
CHAVE PRIMÁRIA(std_id, course_id, mark_type));
Insira alguns registros em alunos, cursos e resultado tabelas. Os valores devem ser inseridos nas tabelas com base nas restrições definidas no momento da criação da tabela.
INSERIR NOS VALORES DOS ALUNOS
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Marquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
INSERIR NOS VALORES DOS CURSOS
( 'CSE-401','Programação Orientada a Objetos',3),
( 'CSE-403','Estrutura de dados',2),
( 'CSE-407','Programação Unix',2);
INSERT INTO resultados VALUES
( '1937463','CSE-401','Exame Interno',quinze),
( '1937463','CSE-401','Exame intermediário',vinte),
( '1937463','CSE-401','Exame final',35),
( '1937464','CSE-403','Exame Interno',17),
( '1937464','CSE-403','Exame intermediário',quinze),
( '1937464','CSE-403','Exame final',30),
( '1937465','CSE-401','Exame Interno',18),
( '1937465','CSE-401','Exame intermediário',2,3),
( '1937465','CSE-401','Exame final',38),
( '1937466','CSE-407','Exame Interno',vinte),
( '1937466','CSE-407','Exame intermediário',22),
( '1937466','CSE-407','Exame final',40);
Aqui, resultado tabela contém vários valores iguais 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 tabela.
SELECIONE*FROM result;
A saída mostra as quatro notas do aluno em três tipos de exame 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 SELECT puder ser escrita com mais eficiência usando a instrução CASE. O SELECT a seguir 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.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Exame Interno'ENTÃO result.marks END) 'Exame Interno',
MAX(CASE WHEN result.mark_type ='Exame intermediário'ENTÃO result.marks END) 'Exame intermediário',
MAX(CASE WHEN result.mark_type ='Exame final'ENTÃO result.marks END) 'Exame final'
FROM resultado
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A seguinte saída aparecerá após a execução da instrução acima, que é mais legível do que a saída anterior.
Gire as linhas para colunas usando CASE e SUM ():
Se você quiser contar o número total de cada curso de cada aluno da tabela, você deve usar a função de agregação SOMA() agrupar por std_id e identidade do curso com a instrução CASE. A consulta a seguir é criada modificando a consulta anterior com a função SUM () e a cláusula GROUP BY.
SELECT result.std_id, result.course_id,MAX(CASE WHEN result.mark_type ='Exame Interno'ENTÃO result.marks END) 'Exame Interno',
MAX(CASE WHEN result.mark_type ='Exame intermediário'ENTÃO result.marks END) 'Exame intermediário',
MAX(CASE WHEN result.mark_type ='Exame final'ENTÃO result.marks END) 'Exame final',
SOMA(result.marks) ComoTotal
FROM resultado
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A saída mostra uma nova coluna chamada Total ou seja, a soma das notas de todos os tipos de exames de cada curso obtidas por cada aluno em particular.
Gire linhas para colunas em várias tabelas:
As duas consultas anteriores são aplicadas ao resultado tabela. Esta tabela está relacionada às outras duas tabelas. Estes são alunos e cursos . Se você deseja exibir o nome do aluno em vez da id do aluno e o nome do curso em vez da id do curso, você deve escrever a consulta SELECT usando três tabelas relacionadas, alunos , cursos e resultado . A consulta SELECT a seguir é criada adicionando três nomes de tabela após a cláusula FORM e definindo as condições apropriadas na cláusula WHERE para recuperar os dados das três tabelas e gerar uma saída mais apropriada do que as consultas SELECT anteriores.
SELECT students.nameComo ``Nome do aluno``, cursos.nomeComo ``Nome do curso``,MAX(CASE WHEN result.mark_type ='Exame Interno'ENTÃO result.marks END) 'CT',
MAX(CASE WHEN result.mark_type ='Exame intermediário'ENTÃO result.marks END) 'Mid',
MAX(CASE WHEN result.mark_type ='Exame final'ENTÃO result.marks END) 'Final',
SOMA(result.marks) ComoTotal
FROM alunos, cursos, resultado
WHERE result.std_id = students.id e result.course_id = course.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
A seguinte saída será gerada 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 consigam transformar quaisquer dados de nível de linha em dados de nível de coluna usando a consulta SELECT depois de ler este artigo.