Comment faire pivoter des lignes en colonnes dans MySQL ?

Explication détaillée de la conversion des lignes et des colonnes du tableau de données MySQL

Présentation

Convertir les données d'un tableau de données dans lequel les lignes représentent les valeurs observées et les colonnes représentent les attributs en un tableau dans lequel les valeurs observées deviennent des colonnes est une technique de manipulation de données très utile. Cet article vous expliquera comment implémenter cette conversion dans MySQL.

Étape 1 : Sélectionnez la colonne cible

Identifiez les colonnes qui seront les valeurs y (lignes du tableau des résultats) et les valeurs x (colonnes du tableau des résultats) dans le tableau croisé dynamique cible. Dans cet exemple, "hostid" sera la valeur y et "itemname" sera la valeur x.

Étape 2 : étendre la table de base pour ajouter des colonnes supplémentaires

Pour chaque valeur x, ajoutez une nouvelle colonne au tableau sous-jacent. Utilisez une expression CASE pour remplir ces colonnes avec la valeur d'élément correspondante basée sur le nom d'élément.

Étape 3 : Regrouper et agréger la table étendue

Regroupez la table étendue par la colonne de valeur y et agrégez la colonne de valeur x à l'aide d'une fonction appropriée (telle que SUM).

Étape 4 : Embellir (facultatif)

Remplacez toutes les valeurs NULL dans le tableau agrégé par une valeur neutre sélectionnée (par exemple 0) pour créer des résultats visuellement plus clairs.

Exemple

Considérez le tableau de base suivant :

hostid	itemname	itemvalue
1	A	10
1	B	3
2	A	9
2	C	40

Créer un tableau croisé dynamique

-- 步骤 1：选择目标列
SELECT hostid, itemname, itemvalue
FROM history;

-- 步骤 2：扩展基础表添加额外列
CREATE VIEW history_extended AS
SELECT
  history.*,
  CASE WHEN itemname = "A" THEN itemvalue END AS A,
  CASE WHEN itemname = "B" THEN itemvalue END AS B,
  CASE WHEN itemname = "C" THEN itemvalue END AS C
FROM history;

-- 步骤 3：分组和聚合扩展表
CREATE VIEW history_itemvalue_pivot AS
SELECT
  hostid,
  SUM(A) AS A,
  SUM(B) AS B,
  SUM(C) AS C
FROM history_extended
GROUP BY hostid;

-- 步骤 4：美化
CREATE VIEW history_itemvalue_pivot_pretty AS
SELECT 
  hostid, 
  COALESCE(A, 0) AS A, 
  COALESCE(B, 0) AS B, 
  COALESCE(C, 0) AS C 
FROM history_itemvalue_pivot;

-- 最终结果
SELECT *
FROM history_itemvalue_pivot_pretty;

Résultats

hostid	A	B	C
1	10	3	0
2	9	0	40

Cet exemple montre comment remodeler efficacement une table dans MySQL pour convertir des lignes en colonnes.

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!