Mysql Pivot Rows Rows to Colonnes

Un tableau de base de données peut stocker différents types de données et parfois nous devons transformer les données au niveau des lignes en données au niveau de la colonne. Ce problème peut être résolu en utilisant la fonction pivot (). Cette fonction est utilisée pour faire tourner les lignes d'une table en valeurs de colonne. Mais cette fonction est prise en charge par très peu de serveurs de base de données, tels que Oracle ou SQL Server. Si vous souhaitez effectuer la même tâche dans la table de la base de données MySQL, vous devez écrire la requête de sélection à l'aide de l'instruction de cas pour faire pivoter les lignes en colonnes. L'article montre la façon de faire la tâche de la fonction pivot () dans les tables de base de données MySQL connexes.

Prérequis:

Vous devez créer une base de données et quelques tables connexes où les lignes d'une table seront converties en colonnes comme 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 tableaux seront liés par une relation un-à-plusieurs et cours et résultats Les tables seront liées par une relation un-à-plusieurs ici. Créer une déclaration du résultat Le tableau contient deux contraintes de clé étrangère pour les champs, std_id, et cours_id.

Créer une base de données UNIDB;
Utilisez UNIDB;
Créer des étudiants de table (
id int clé primaire,
nom varchar (50) pas null,
Département Varchar (15) pas nul);
Créer des cours de table (
Course_id Varchar (20) Clé primaire,
nom varchar (50) pas null,
crédit petit non nul);
Créer un résultat de table (
std_id int not null,
Course_id varchar (20) pas null,
mark_type varchar (20) pas null,
Marque SmallInt pas nul,
La clé étrangère (STD_ID) fait référence aux étudiants (ID),
CLÉS ARRÊTRE (COURS_ID) RÉFÉRENCES COURS (COURS_ID),
Clé primaire (std_id, cours_id, mark_type));

Insérer certains enregistrements dans étudiants, cours et résultats les tables. Les valeurs doivent être insérées dans les tables en fonction des restrictions fixées au moment de la création de table.

Insérer dans les valeurs des étudiants
(«1937463», «Harper Lee», «CSE»),
(«1937464», «Garcia Marquez», «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», «Unix Programming», 2);
Insérer dans les valeurs de résultat
(«1937463», «CSE-401», «Examen interne», 15),
(«1937463», «CSE-401», «Examen à mi-parcours», 20),
(«1937463», «CSE-401», «Examen final», 35),
(«1937464», «CSE-403», «Examen interne», 17),
(«1937464», «CSE-403», «Examen à mi-parcours», 15),
(«1937464», «CSE-403», «Examen final», 30),
(«1937465», «CSE-401», «Examen interne», 18),
(«1937465», «CSE-401», «Examen à mi-parcours», 23),
(«1937465», «CSE-401», «Examen final», 38),
(«1937466», «CSE-407», «Examen interne», 20),
(«1937466», «CSE-407», «Examen à mi-parcours», 22),
(«1937466», «CSE-407», «Examen final», 40);

Ici, résultat Le tableau contient plusieurs mêmes valeurs pour std_id, mark_type et cours_id colonnes de chaque ligne. Comment convertir ces lignes en colonnes de ce tableau pour afficher les données dans un format plus organisé est affichée dans la partie suivante de ce tutoriel.

Faites tourner les lignes vers les colonnes à l'aide de l'instruction de cas:

Exécutez l'instruction SELECT simple suivante pour afficher tous les enregistrements du résultat tableau.

Sélectionner * dans le résultat;

La sortie montre les quatre marques de l'étudiant pour trois types d'examen de trois cours. Donc les valeurs de std_id, cours_id et mark_type 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 sélectionnée peut être écrite plus efficacement en utilisant l'instruction de cas. La sélection suivante avec l'instruction Case transformera les valeurs répétitives des lignes en noms de colonne et affichera le contenu des tables dans un format plus compréhensible pour l'utilisateur.

Sélectionner le résultat.std_id, résultat.cours_id,
Max (cas quand le résultat.mark_type = "examen interne" puis résultat.Marques fin) "Examen interne",
Max (cas quand le résultat.mark_type = "examen à terme" puis résultat.Marques fin) "Examen à mi-parcours",
Max (cas quand le résultat.mark_type = "examen final" puis résultat.Marques fin) "Examen final"
Du résultat
Groupe par résultat.std_id, résultat.cours_id
Ordre par résultat.std_id, résultat.Course_id ASC;

La sortie suivante apparaîtra après avoir exécuté l'instruction ci-dessus qui est plus lisible que la sortie précédente.

Faites tourner les lignes vers des colonnes à l'aide de cas et de sum ():

Si vous souhaitez compter le nombre total de chaque cours de chaque élève de la table, vous devez utiliser la fonction agrégée SOMME() par groupe std_id et cours_id avec la déclaration de cas. La requête suivante est créée en modifiant la requête précédente avec la fonction SUM () et le groupe par clause.

Sélectionner le résultat.std_id, résultat.cours_id,
Max (cas quand le résultat.mark_type = "examen interne" puis résultat.Marques fin) "Examen interne",
Max (cas quand le résultat.mark_type = "examen à terme" puis résultat.Marques fin) "Examen à mi-parcours",
Max (cas quand le résultat.mark_type = "examen final" puis résultat.Marques fin) "Examen final",
Somme (résultat.marques) comme total
Du résultat
Groupe par résultat.std_id, résultat.cours_id
Ordre par résultat.std_id, résultat.Course_id ASC;

La sortie montre une nouvelle colonne nommée Total qui affiche la somme des marques de tous les types d'examen de chaque cours obtenu par chaque étudiant en particulier.

Faites tourner les lignes vers des colonnes dans plusieurs tables:

Les deux requêtes précédentes sont appliquées à la résultat tableau. Ce tableau est lié aux deux autres tables. Ceux-ci sont étudiants et cours. Si vous souhaitez afficher le nom de l'étudiant au lieu de l'identification étudiante et du nom de cours au lieu de l'identification du cours, vous devez écrire la requête sélectionnée à l'aide de trois tables connexes, étudiants, cours et résultat. La requête de sélection suivante est créée en ajoutant trois noms de table après la clause de formulaire et en définissant les conditions appropriées dans la clause où récupérer les données des trois tables et générer une sortie plus appropriée que les requêtes de sélection précédents.

Sélectionner les étudiants.Nom comme «nom étudiant», cours.nom comme «nom de cours»,
Max (cas quand le résultat.mark_type = "examen interne" puis résultat.Marques fin) "CT",
Max (cas quand le résultat.mark_type = "examen à terme" puis résultat.Marques fin) "Mid",
Max (cas quand le résultat.mark_type = "examen final" puis résultat.Marques fin) "Final",
Somme (résultat.marques) comme total
Des étudiants, des cours, des résultats
Où le résultat.std_id = étudiants.ID et résultat.cours_id = cours.cours_id
Groupe par résultat.std_id, résultat.cours_id
Ordre par résultat.std_id, résultat.Course_id ASC;

La sortie suivante générera après avoir exécuté la requête ci-dessus.

Conclusion:

Comment vous pouvez implémenter la fonctionnalité de la fonction pivot () sans le support de la fonction pivot () dans MySQL est montré dans cet article en utilisant des données factice. J'espère que les lecteurs pourront transformer toutes les données au niveau des lignes en données au niveau de la colonne en utilisant la requête sélectionnée après avoir lu cet article.