Comment faire pivoter des colonnes en lignes dans MySQL pour le reporting ?

MySQL Pivot : convertir les colonnes en lignes

Dans les bases de données relationnelles telles que MySQL, les données doivent souvent être transformées à des fins de reporting. L'une de ces transformations consiste à convertir des colonnes en lignes, un processus appelé « pivotement ». Cette technique permet une représentation des données plus flexible et concise.

Question :

Considérez la table MySQL suivante :

<code class="language-sql">CREATE TABLE mytable (
  id INT, 
  month VARCHAR(3), 
  col1 VARCHAR(1), 
  col2 VARCHAR(1), 
  col3 VARCHAR(1), 
  col4 VARCHAR(1)
);

INSERT INTO mytable (id, month, col1, col2, col3, col4) VALUES
(101, 'Jan', 'A', 'B', NULL, 'B'),
(102, 'Feb', 'C', 'A', 'G', 'E');</code>

L'objectif est de créer un rapport qui affiche les valeurs de col1 à col4 sous forme de lignes et les mois de janvier et février sous forme de colonnes.

Solution :

MySQL ne fournit pas de fonctions intégrées pour les données non pivotantes ou pivotantes. Cependant, nous pouvons simuler ces opérations en utilisant UNION ALL et des agrégats avec des expressions CASE.

1. Anti-perspective :

Pour annuler le pivotement des données, nous créons une nouvelle sous-requête qui utilise UNION ALL pour combiner les lignes de toutes les colonnes en une seule colonne :

<code class="language-sql">SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
UNION ALL
SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
UNION ALL
SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
UNION ALL
SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
FROM mytable;</code>

2. Point de vue :

Ensuite, nous enveloppons la requête non pivot dans une sous-requête et utilisons des agrégats et des instructions CASE pour transformer les données dans le format requis :

<code class="language-sql">SELECT descrip, 
  MAX(CASE WHEN month = 'Jan' THEN `value` ELSE NULL END) AS Jan,
  MAX(CASE WHEN month = 'Feb' THEN `value` ELSE NULL END) AS Feb
FROM (
  SELECT id, month, `value`, descrip
  FROM (
    SELECT id, month, col1 AS `value`, 'col1' AS `descrip`
    UNION ALL
    SELECT id, month, col2 AS `value`, 'col2' AS `descrip`
    UNION ALL
    SELECT id, month, col3 AS `value`, 'col3' AS `descrip`
    UNION ALL
    SELECT id, month, col4 AS `value`, 'col4' AS `descrip`
    FROM mytable
  ) AS unpivoted
) AS src
GROUP BY descrip;</code>

Résultat :

Descrip	Jan	Feb
col1	A	C
col2	B	A
col3	NULL	G
col4	B	E

Remarque : remplacez ELSE 0 END par ELSE NULL END pour rendre le résultat plus cohérent avec les spécifications de la base de données et éviter les valeurs 0 inutiles.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!