Prérequis:
Vous devez créer une base de données et des tables associées où les lignes d'une table seront converties en colonnes comme la fonction PIVOT(). Exécutez les instructions SQL suivantes pour créer une base de données nommée ' unidb ' et créez trois tables nommées ' étudiants ',' cours ' et ' résultat '. étudiants et résultat les tables seront liées par une relation un-à-plusieurs et cours et résultats les tables seront liées par une relation un-à-plusieurs ici. CREATE déclaration de la résultat table contient deux contraintes de clé étrangère pour les champs, id_std , et id_cours .
CRÉER UNE BASE DE DONNÉES unidb ;
UTILISER unidb ;
CRÉER TABLE étudiants(
identifiantINT CLÉ PRIMAIRE,
nom varchar(cinquante)NON NULL,
département VARCHAR(quinze)NON NULL);
CRÉER des cours TABLE(
id_cours VARCHAR(vingt)CLÉ PRIMAIRE,
nom varchar(cinquante)NON NULL,
crédit SMALLINT NON NULL);
CRÉER TABLE résultat(
std_id INT NON NULL,
id_cours VARCHAR(vingt)NON NULL,
mark_type VARCHAR(vingt)NON NULL,
marque SMALLINT NOT NULL,
CLÉ ÉTRANGÈRE(id_std)RÉFÉRENCES étudiants(identifiant),
CLÉ ÉTRANGÈRE(id_cours)RÉFÉRENCES cours(id_cours),
CLÉ PRIMAIRE(std_id, course_id, mark_type));
Insérer des enregistrements dans étudiants, cours et résultat les tables. Les valeurs doivent être insérées dans les tables en fonction des restrictions définies au moment de la création de la table.
INSÉRER DANS LES VALEURS DES ÉTUDIANTS
( '1937463','Harper Lee','CSE'),
( '1937464','Garcia Márquez','CSE'),
( '1937465','Forster, E.M.','CSE'),
( '1937466','Ralph Ellison','CSE');
INSÉRER DANS LES VALEURS des cours
( 'CSE-401','Programmation orientée objet',3),
( 'CSE-403','Structure de données',2),
( 'CSE-407','Programmation Unix',2);
INSÉRER DANS LES VALEURS DE RÉSULTAT
( '1937463','CSE-401',« Examen interne »,quinze),
( '1937463','CSE-401','Examen de mi-saison',vingt),
( '1937463','CSE-401','Examen final',35),
( '1937464','CSE-403',« Examen interne »,17),
( '1937464','CSE-403','Examen de mi-saison',quinze),
( '1937464','CSE-403','Examen final',30),
( '1937465','CSE-401',« Examen interne »,18),
( '1937465','CSE-401','Examen de mi-saison',2. 3),
( '1937465','CSE-401','Examen final',38),
( '1937466','CSE-407',« Examen interne »,vingt),
( '1937466','CSE-407','Examen de mi-saison',22),
( '1937466','CSE-407','Examen final',40);
Ici, résultat le tableau contient plusieurs mêmes valeurs pour id_std , type_marque et id_cours colonnes dans chaque ligne. Comment convertir ces lignes en colonnes de ce tableau pour afficher les données dans un format plus organisé est indiqué dans la partie suivante de ce didacticiel.
Faites pivoter les lignes en colonnes à l'aide de l'instruction CASE :
Exécutez l'instruction SELECT simple suivante pour afficher tous les enregistrements du résultat table.
SÉLECTIONNER*À PARTIR du résultat ;
La sortie affiche les notes des quatre étudiants pour trois types d'examens de trois cours. Donc les valeurs de id_std , id_cours et type_marque sont répétés plusieurs fois pour les différents étudiants, cours et types d'examens.
La sortie sera plus lisible si la requête SELECT peut être écrite plus efficacement à l'aide de l'instruction CASE. Le SELECT suivant avec l'instruction CASE transformera les valeurs répétées des lignes en noms de colonnes et affichera le contenu des tableaux dans un format plus compréhensible pour l'utilisateur.
SELECT result.std_id, result.course_id,MAXIMUM(CASE WHEN result.mark_type =« Examen interne »ALORS result.marks END) « Examen interne »,
MAXIMUM(CASE WHEN result.mark_type ='Examen de mi-saison'ALORS result.marks END) 'Examen de mi-saison',
MAXIMUM(CASE WHEN result.mark_type ='Examen final'ALORS result.marks END) 'Examen final'
DE résultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
La sortie suivante apparaîtra après l'exécution de l'instruction ci-dessus qui est plus lisible que la sortie précédente.
Faites pivoter les lignes en colonnes à l'aide de CASE et SUM() :
Si vous voulez compter le nombre total de chaque cours de chaque étudiant du tableau, vous devez utiliser la fonction d'agrégat SOMME() par groupe id_std et id_cours avec l'instruction CASE. La requête suivante est créée en modifiant la requête précédente avec la fonction SUM() et la clause GROUP BY.
SELECT result.std_id,result.course_id,MAXIMUM(CASE WHEN result.mark_type =« Examen interne »ALORS result.marks END) « Examen interne »,
MAXIMUM(CASE WHEN result.mark_type ='Examen de mi-saison'ALORS result.marks END) 'Examen de mi-saison',
MAXIMUM(CASE WHEN result.mark_type ='Examen final'ALORS result.marks END) 'Examen final',
SOMME(résultat.marques) commeLe total
DE résultat
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
La sortie affiche une nouvelle colonne nommée Le total qui affiche la somme des notes de tous les types d'examen de chaque cours obtenues par chaque étudiant en particulier.
Faire pivoter les lignes en colonnes dans plusieurs tableaux :
Les deux requêtes précédentes sont appliquées au résultat table. Ce tableau est lié aux deux autres tableaux. Ceux-ci sont étudiants et cours . Si vous souhaitez afficher le nom de l'étudiant au lieu de l'identifiant de l'étudiant et le nom du cours au lieu de l'identifiant du cours, vous devez écrire la requête SELECT en utilisant trois tables liées, étudiants , cours et résultat . La requête SELECT suivante est créée en ajoutant trois noms de table après la clause FORM et en définissant des conditions appropriées dans la clause WHERE pour récupérer les données des trois tables et générer une sortie plus appropriée que les requêtes SELECT précédentes.
SELECT étudiants.nomcomme ``Nom d'étudiant``, cours.nomcomme ``Nom du cours``,MAXIMUM(CASE WHEN result.mark_type =« Examen interne »ALORS result.marks END) 'CT',
MAXIMUM(CASE WHEN result.mark_type ='Examen de mi-saison'ALORS result.marks END) 'Milieu',
MAXIMUM(CASE WHEN result.mark_type ='Examen final'ALORS result.marks END) 'Final',
SOMME(résultat.marques) commeLe total
DES étudiants, cours, résultat
O result.std_id = étudiants.id et result.course_id= courses.course_id
GROUP BY result.std_id, result.course_id
ORDER BY result.std_id, result.course_id ASC;
La sortie suivante sera générée après l'exécution de la requête ci-dessus.
Conclusion:
La façon dont vous pouvez implémenter la fonctionnalité de la fonction Pivot() sans la prise en charge de la fonction Pivot() dans MySQL est illustrée dans cet article en utilisant des données factices. J'espère que les lecteurs pourront transformer toutes les données au niveau des lignes en données au niveau des colonnes en utilisant la requête SELECT après avoir lu cet article.