Comment changer le formulaire de tableau pour convertir des lignes en colonnes dans MySQL

MySQL est l’un des systèmes de gestion de bases de données relationnelles les plus populaires au monde. MySQL nous permet de stocker, de gérer et d’analyser des données sous forme de tableaux. Une table se compose de lignes et de colonnes qui représentent des entités et des attributs de données. Cependant, nous devons parfois modifier la forme du tableau pour convertir les lignes en colonnes ou vice versa. C’est ce qu’on appelle la transposition de table.

Les tableaux de transposition peuvent être utiles pour plusieurs raisons, telles que :

• Simplifiez la visualisation et la compréhension des données
• Optimiser les performances des requêtes et des index
• Adaptez les structures de données aux besoins de l’entreprise ou des applications
• Effectuer des opérations d’agrégation ou de calcul sur des données

Cependant, MySQL ne dispose pas d’une fonction intégrée permettant de transposer directement les tables. Par conséquent, nous devons utiliser certaines techniques ou astuces pour atteindre cet objectif. Dans cet article, nous allons discuter de plusieurs façons de changer la forme d’un tableau pour convertir des lignes en colonnes dans MySQL, ainsi que des exemples et des explications.

Méthode 1 : Utilisation de la fonction d’agrégation et de la clause GROUP BY

Une façon de changer la forme d’une table pour convertir des lignes en colonnes dans MySQL est d’utiliser la fonction d’agrégation et la clause GROUP BY. Une fonction d’agrégation est une fonction qui prend un ensemble de valeurs en entrée et renvoie une seule valeur en sortie. Certaines fonctions d’agrégation couramment utilisées dans MySQL sont les suivantes :

• SUM() : Calcule le nombre total d’un ensemble de valeurs
• AVG() : Calcule la moyenne d’un ensemble de valeurs
• MIN() : Renvoie la plus petite valeur d’un ensemble de valeurs
• MAX() : Renvoie la plus grande valeur d’un ensemble de valeurs
• COUNT() : Compte le nombre de valeurs présentes dans un ensemble de valeurs

Une clause GROUP BY est une clause utilisée pour regrouper des lignes qui ont la même valeur dans une ou plusieurs colonnes. La clause GROUP BY est généralement utilisée conjointement avec une fonction d’agrégation pour calculer les valeurs agrégées de chaque groupe.

Pour modifier le formulaire de la table afin de convertir les lignes en colonnes à l’aide de la fonction d’agrégation et de la clause GROUP BY, nous devons effectuer les étapes suivantes :

• Spécifiez la colonne qui sera la nouvelle ligne dans le tableau des résultats. Cette colonne est généralement une colonne qui a des valeurs différentes et pas trop. Par exemple, les colonnes d’année, de mois, de catégorie, de type, etc.
• Spécifiez la colonne qui sera la nouvelle colonne dans la table de résultats. Il s’agit généralement d’une colonne qui a la même valeur ou une valeur limitée pour chaque ligne. Par exemple, les colonnes de nom, de produit, de marque, etc.
• Spécifiez la fonction d’agrégation qui sera utilisée pour calculer la valeur de chaque nouvelle combinaison de lignes et de colonnes. Cette fonction d’agrégation dépend du type de données et de l’objectif de notre analyse. Par exemple, SUM(), AVG(), MIN(), MAX(), etc.
• Créez une requête SQL qui utilise la fonction d’agrégation et la clause GROUP BY pour renvoyer la table de résultats souhaitée.

Prenons un exemple pour mieux comprendre cela. Supposons que nous ayons le tableau suivant qui stocke les données sur les ventes de produits pour chaque mois et chaque catégorie :

Ce tableau comporte trois colonnes : mois, catégorie et ventes. Nous voulons modifier la forme de ce tableau pour convertir les lignes en colonnes, afin de pouvoir voir les ventes totales pour chaque catégorie et chaque mois dans un seul tableau. Le tableau de résultats que nous voulons est le suivant :

Pour atteindre cet objectif, nous pouvons utiliser la méthode 1 avec les étapes suivantes :

• Spécifiez la colonne qui sera la nouvelle ligne dans le tableau des résultats. Dans ce cas, la colonne de catégorie sera une nouvelle ligne, car elle a des valeurs différentes et pas trop (A, B, C).
• Spécifiez la colonne qui sera la nouvelle colonne dans la table de résultats. Dans ce cas, la colonne du mois sera la nouvelle colonne, car elle a la même valeur ou une valeur limitée pour chaque ligne (janvier, février, mars).
• Spécifiez la fonction d’agrégation qui sera utilisée pour calculer la valeur de chaque nouvelle combinaison de lignes et de colonnes. Dans ce cas, nous utiliserons la fonction SUM() pour calculer les ventes totales pour chaque catégorie et chaque mois.
• Créez une requête SQL qui utilise la fonction d’agrégation et la clause GROUP BY pour renvoyer la table de résultats souhaitée. Les requêtes SQL que nous pouvons utiliser sont les suivantes :

SELECT
category
SUM(CASE WHEN month ='Jan' THEN sales ELSE 0 END) AS Jan,
SUM(CASE WHEN month ='Feb' THEN sales ELSE 0 END) AS Feb,
SUM(CASE WHEN month ='Mar' THEN sales ELSE 0 END) AS Mar
FROM
sales_table
GROUP BY
category;

Cette requête effectue les opérations suivantes :

• Sélectionnez la colonne de catégorie dans le tableau sales_table
• Utilisez les fonctions SUM() et CASE pour calculer les ventes totales pour chaque catégorie et chaque mois. CASE est une instruction conditionnelle qui renvoie une valeur en fonction d’une condition donnée. Dans cette requête, nous utilisons CASE pour vérifier la valeur mensuelle et renvoyer la valeur des ventes le cas échéant, ou 0 dans le cas contraire. Par exemple, SUM(CASE WHEN month =’Jan’ THEN sales ELSE 0 END) renverra le total des ventes de janvier pour chaque catégorie, ou 0 s’il n’y a pas de ventes pour ce mois.
• Donnez un nom d’alias à chaque nouvelle colonne à l’aide de AS. Alias est un nom alternatif que l’on peut donner à une colonne ou à une table pour faciliter l’écriture et la lecture d’une requête. Dans cette requête, nous donnons un nom d’alias à chaque nouvelle colonne avec un nom de mois correspondant, tel que Jan, Feb et Mar.
• Utilisez la clause GROUP BY pour regrouper les lignes en fonction de la colonne de catégorie. La clause GROUP BY crée une ligne pour chaque valeur unique de la colonne de catégorie et calcule une valeur agrégée pour toutes les autres colonnes.

Cette requête générera la table de résultats souhaitée, comme indiqué ci-dessus.

Méthode 2 : Utilisation de la fonction de pivot

Une autre façon de changer la forme d’un tableau pour convertir des lignes en colonnes dans MySQL est d’utiliser la fonction pivot. La fonction pivot est une fonction qui nous permet de changer la forme d’un tableau en faisant pivoter les données des lignes vers les colonnes ou vice versa. La fonction pivot permet de transposer facilement et rapidement des tableaux.

Cependant, MySQL n’a pas de fonction de pivot intégrée que nous pouvons utiliser directement. Par conséquent, nous devons utiliser certaines extensions ou plugins pour ajouter des fonctions de pivot à MySQL. L’une des extensions les plus populaires et les plus fréquemment utilisées est Flexviews. Flexviews est une extension qui fournit des fonctions de pivotement, de dépivotement et d’actualisation incrémentielle pour MySQL. Flexviews peut être téléchargé et installé à partir de son site officiel : https://github.com/greenlion/swanhart-tools/tree/master/flexviews

Pour modifier la forme d’un tableau afin de convertir des lignes en colonnes à l’aide de la fonction pivot, nous devons procéder comme suit :

• Installez et activez l’extension Flexviews dans MySQL. Suivez les instructions d’installation données sur son site officiel.
• Créez une table temporaire qui contient les données que nous voulons mettre en forme. Les tables temporaires sont des tables qui n’existent que pendant une session MySQL et qui sont automatiquement supprimées à la fin de la session. Les tables temporaires peuvent être créées à l’aide de la commande CRÉER TABLE TEMPORAIRE. Par exemple, nous pouvons créer une table temporaire avec le nom sales_temp contenant les données de la table sales_table avec la commande suivante :

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Créez une vue qui contient la définition de la fonction de pivot que nous voulons utiliser. Une vue est un objet de base de données qui stocke les résultats d’une requête SQL sous la forme d’une table virtuelle. Les vues peuvent être créées à l’aide de la commande CREATE VIEW. Par exemple, nous pouvons créer une vue avec le nom pivot_view qui contient la définition de la fonction pivot pour modifier la forme de la table sales_temp à l’aide de la commande suivante :

CREATE VIEW pivot_view AS
SELECT
FVPivot$(
'sales_temp', -- nom de la table temporaire qui contient les données
'category', -- le nom de la colonne qui sera la nouvelle ligne
'month', -- le nom de la colonne qui deviendra la nouvelle colonne
'sales', -- nom de la colonne qui contient la valeur à calculer
'SUM', -- le nom de la fonction d'agrégation à utiliser
NULL -- noms de colonnes supplémentaires à inclure dans le tableau des résultats (facultatif)
) AS pivot_result; -- nom d'alias pour le résultat de la fonction de pivot

• Exécutez une requête SQL pour appeler la fonction pivot de la vue qui a été créée. Les requêtes SQL que nous pouvons utiliser sont les suivantes :

SELECT * FROM pivot_view;

Cette requête générera la table de résultats souhaitée, comme indiqué ci-dessus.

Méthode 3 : Utilisation des fonctions Unpivot et Pivot

La troisième façon de changer la forme d’un tableau pour convertir des lignes en colonnes dans MySQL est d’utiliser les fonctions unpivot et pivot. La fonction de non-pivot est la fonction opposée de la fonction de pivot, qui modifie la forme de la table en faisant pivoter les données des colonnes vers les lignes. La fonction de dépivotement peut être utilisée pour transposer les tableaux de manière plus flexible et dynamique.

Cependant, MySQL n’a pas non plus de fonction de dépivot intégrée que nous pouvons utiliser directement. Par conséquent, nous devons également utiliser certaines extensions ou plugins pour ajouter une fonctionnalité de dépivotement à MySQL. L’une des extensions les plus populaires et les plus fréquemment utilisées est la Flexviews dont nous avons parlé précédemment. Flexviews fournit une fonction de décroisement en plus des fonctions de pivot et d’actualisation incrémentielle pour MySQL.

Pour modifier la forme du tableau afin de convertir les lignes en colonnes à l’aide des fonctions unpivot et pivot, nous devons procéder comme suit :

• Installez et activez l’extension Flexviews dans MySQL si cela n’a pas encore été fait. Suivez les instructions d’installation données sur son site officiel
• Créez une table temporaire qui contient les données que nous voulons mettre en forme, si elle n’existait pas déjà auparavant. Les tables temporaires peuvent être créées à l’aide de la commande CRÉER TABLE TEMPORAIRE. Par exemple, nous pouvons créer une table temporaire avec le nom sales_temp contenant les données de la table sales_table avec la commande suivante :

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Créez une vue qui contient la définition de la fonction de dépermutation que nous voulons utiliser. Une vue est un objet de base de données qui stocke les résultats d’une requête SQL sous la forme d’une table virtuelle. Les vues peuvent être créées à l’aide de la commande CREATE VIEW. Par exemple, nous pouvons créer une vue avec le nom unpivot_view contenant une définition de fonction de non-pivot pour modifier la forme d’une table sales_temp avec la commande suivante :

CREATE VIEW unpivot_view AS
SELECT
FVUnpivot $(
'sales_temp', -- nom de la table temporaire qui contient les données
'category', -- le nom de la colonne qui restera dans la ligne
'month', -- le nom de la nouvelle colonne qui stockera le nom de l'ancienne colonne
'sales', -- le nom de la nouvelle colonne qui stockera les anciennes valeurs de colonne
'month, sales' -- le nom de l'ancienne colonne à convertir en ligne
) AS unpivot_result; -- nom d'alias pour les résultats de la fonction de non-pivot

• Créez une autre vue qui contient la définition de la fonction de pivot que nous voulons utiliser. Les vues peuvent être créées à l’aide de la commande CREATE VIEW. Par exemple, nous pouvons créer une vue avec le nom pivot_view contenant la définition de la fonction pivot pour modifier la forme de la table unpivot_view avec la commande suivante :

CREATE VIEW pivot_view AS
SELECT
FVPivot$(
'unpivot_view', -- le nom d'affichage qui contient les données
'category', -- le nom de la colonne qui sera la nouvelle ligne
'month', -- le nom de la colonne qui deviendra la nouvelle colonne
'sales', -- nom de la colonne qui contient la valeur à calculer
'SUM', -- le nom de la fonction d'agrégation à utiliser
NULL -- noms de colonnes supplémentaires à inclure dans le tableau des résultats (facultatif)
) AS pivot_result; -- nom d'alias pour le résultat de la fonction de pivot

• Exécutez une requête SQL pour appeler la fonction pivot de la vue qui a été créée. Les requêtes SQL que nous pouvons utiliser sont les suivantes :

SELECT * FROM pivot_view;

Cette requête générera la table de résultats souhaitée, comme indiqué ci-dessus.

Conclusion

Dans cet article, nous avons discuté de plusieurs façons de modifier la forme d’un tableau pour convertir des lignes en colonnes dans MySQL, ainsi que d’exemples et d’explications. Voici quelques-unes des façons dont nous pouvons l’utiliser :

• Utilisation des fonctions d’agrégation et des clauses GROUP BY
• Utiliser la fonction de pivot
• Utiliser les fonctions de dépivotement et de pivot

Chaque méthode a ses avantages et ses inconvénients, en fonction de la structure des données, de l’objectif de l’analyse et de nos préférences. Nous pouvons choisir la méthode qui convient le mieux à nos besoins et à nos conditions.

J’espère que cet article vous sera utile et pourra vous aider à transposer des tableaux dans MySQL. Merci d’avoir lu cet article jusqu’au bout.