Tableau croisé dynamique dans MySQL

1. Créer un tableau croisé dynamique dans MySQL à l’aide de la fonction MAX et de l’instruction IF
2. Créer un tableau croisé dynamique dans MySQL à l’aide d’une instruction CASE
3. Créer un tableau croisé dynamique dans MySQL à l’aide de colonnes dynamiques

Dans cet article, nous décrivons comment transformer les données d’un tableau de lignes en colonnes. Ce processus est appelé pivotement. Le résultat de cette transformation est généralement un tableau récapitulatif dans lequel nous présentons des informations adaptées à la génération de rapports.

Dans MySQL, il n’y a pas de fonction intégrée pour créer des tableaux croisés dynamiques, vous devrez donc écrire une requête MySQL pour générer un tableau croisé dynamique. Heureusement, il existe trois façons différentes de créer un tableau croisé dynamique à l’aide de MySQL.

1. Créez un tableau croisé dynamique dans MySQL à l’aide de l’instruction IF
2. Créez un tableau croisé dynamique dans MySQL à l’aide d’une instruction CASE
3. Créer un tableau croisé dynamique dans MySQL à l’aide de colonnes dynamiques

Le script suivant crée une table student avec trois colonnes (name, subjectid et marks).

create table student(name varchar(20),subjectid int(10), marks int(10));

Ici, nous insérons des exemples de valeurs de données dans la table des étudiants pour la démonstration.

insert into student values ('Sam',1,70);
insert into student values ('Sam',2,77);
insert into student values ('Sam',3,71);
insert into student values ('Sam',4,70);
insert into student values ('Sam',1,99);
insert into student values ('John',1,89);
insert into student values ('John',2,87);
insert into student values ('John',3,88);
insert into student values ('John',4,89); 
insert into student values ('Martin',1,60);
insert into student values ('Martin',2,47);
insert into student values ('Martin',3,68);
insert into student values ('Martin',4,39);

Après avoir inséré ces valeurs, écrivons une requête select pour voir les enregistrements dans la base de données.

Select * from student;

La sortie sera :

Créer un tableau croisé dynamique dans MySQL à l’aide de la fonction MAX et de l’instruction IF

C’est une approche très simple du problème. Dans cette approche, nous utiliserons la fonction MAX et l’instruction IF. Si plusieurs entrées pour une même matière sont présentes pour un étudiant dans la base de données, cela prendra un maximum de deux points.

Voyons maintenant comment l’utiliser avec la requête SELECT :

SELECT name,
MAX(IF(subjectid=1, marks, NULL)) AS Sub1,
MAX(IF(subjectid=2, marks, NULL)) AS Sub2,
MAX(IF(subjectid=3, marks, NULL)) AS Sub3,
MAX(IF(subjectid=4, marks, NULL)) AS Sub4
FROM student
GROUP BY name;

La sortie sera :

Cette sortie est un résultat compilé pour chaque sujet étudiant. Puisque vous voulez une ligne pour chaque élève, vous devez regrouper par colonne de nom. De plus, vous devez spécifier une condition pour chaque colonne, c’est-à-dire une condition par sujet.

Créer un tableau croisé dynamique dans MySQL à l’aide d’une instruction CASE

C’est aussi une approche directe du problème. Nous utiliserons la fonction MAX avec l’instruction CASE dans cette approche. De plus, si plusieurs entrées pour une même matière sont présentes pour un étudiant dans la base de données, cela prendra un maximum de deux points. De même, si les notes n’existent pas pour certains étudiants, elle prendra la valeur NULL.

Voyons maintenant comment l’utiliser avec la requête SELECT :

SELECT name,
MAX(CASE WHEN subjectid=1 THEN marks ELSE NULL END) AS Sub1,
MAX(CASE WHEN subjectid=2 THEN marks ELSE NULL END) AS Sub2,
MAX(CASE WHEN subjectid=3 THEN marks ELSE NULL END) AS Sub3,
MAX(CASE WHEN subjectid=4 THEN marks ELSE NULL END) AS Sub4
FROM student
GROUP BY name; 

La sortie sera :

Créer un tableau croisé dynamique dans MySQL à l’aide de colonnes dynamiques

Une fonction d’agrégat (MAX), une instruction IF et une instruction CASE ont généré le tableau croisé dynamique dans l’exemple ci-dessus. L’inconvénient d’utiliser cette approche est que nous devons connaître les en-têtes de colonnes lors de l’écriture de la requête, et lorsque le nombre de colonnes augmente, le code augmente également. Nous pouvons opter pour les deux approches ci-dessus pour des résultats plus petits et toutes les valeurs possibles.

Ainsi, pour surmonter ces limitations, nous pouvons utiliser des colonnes pivots dynamiques. Ici, la fonction GROUP_CONCAT peut générer dynamiquement les colonnes d’une sortie de table PIVOT.

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT CONCAT(
 ' MAX(CASE WHEN subjectid = ', subjectid, ' THEN marks ELSE 0 END) 
 AS "', subjectid, '"')
)
INTO @sql FROM student;

SET @sql = CONCAT('SELECT name, ', @sql, 
 ' FROM student GROUP BY name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Dans une fonction GROUP_CONCAT, nous utilisons le mot-clé DISTINCT pour obtenir une liste unique de marques. Sinon, notre requête donnerait une erreur car la liste est trop longue. Vous pouvez écrire Select * from @sql pour vérifier la requête dynamique à tout moment.

Maintenant, lorsque nous exécutons cette requête, voici ce que nous obtenons :

Notez que les en-têtes de colonnes sont générés dynamiquement en fonction des valeurs du tableau et que l’en-tête de colonne représente le subjectid.