MySQL - Convertir des lignes en colonnes

Question

J'ai essayé de rechercher des publications mais je n'ai trouvé que des solutions pour SQL Server/Access. J'ai besoin d'une solution dans MySQL (5.X).

J'ai une table (appelée history) avec 3 colonnes : hostid, itemname, itemvalue.

Si j'effectue une sélection (select * from History), elle renvoie

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

Comment interroger une base de données pour renvoyer un contenu similaire

+--------+------+-----+-----+
   | hostid |   A  |  B  |  C  |
   +--------+------+-----+-----+
   |   1    |  10  |  3  |  0  |
   +--------+------+-----+-----+
   |   2    |   9  |  0  |  40 |
   +--------+------+-----+-----+

Answer 1

J'ajouterai une description plus longue et plus détaillée des étapes pour résoudre ce problème. Je m'excuse si c'est trop long.

---

Je vais commencer par la base que vous avez donnée et l'utiliser pour définir quelques termes que j'utiliserai tout au long du reste de cet article. Ce sera la table de base :

select * from history;

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

Ce sera notre objectif, Joli tableau croisé dynamique :

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

La valeur dans la colonne

history.hostid deviendra la history.hostid 列中的值将成为数据透视表中的y 值。 history.itemnamey value dans le tableau croisé dynamique. Les valeurs de la colonne history.itemname deviendront x-values

(pour des raisons évidentes).

---

Lorsque je dois résoudre le problème de la création d'un tableau croisé dynamique, je le résous en utilisant un processus en trois étapes (avec une quatrième étape facultative) :

1. Sélectionnez les colonnes qui vous intéressent, c'est-à-dire valeurs y et valeursx
2. Étendez la table de base avec des colonnes supplémentaires - une colonne pour chaque x valeur
3. Tables étendues de groupe et d'agrégation - un ensemble par
y valeur
(Facultatif) Embellissez la table globale

Appliquons ces étapes à votre problème et voyons ce que nous obtenons :

Étape 1 : Sélectionnez la colonne qui vous intéressehostid 提供y 值，itemname. Dans le résultat souhaité, hostid fournit la valeur y et

fournit la valeur

x . itemname

Étape 2 : étendez la table de base avec des colonnes supplémentaires

. Nous voulons généralement une colonne pour chaque valeur x. Rappelez-vous que notre colonne de valeur x est NULL 的模式 - itemname = "A" 的行的新列 A :

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
);

select * from history_extended;

+--------+----------+-----------+------+------+------+
| hostid | itemname | itemvalue | A    | B    | C    |
+--------+----------+-----------+------+------+------+
|      1 | A        |        10 |   10 | NULL | NULL |
|      1 | B        |         3 | NULL |    3 | NULL |
|      2 | A        |         9 |    9 | NULL | NULL |
|      2 | C        |        40 | NULL | NULL |   40 |
+--------+----------+-----------+------+------+------+

Notez que nous n'avons pas modifié le nombre de lignes - nous avons simplement ajouté des colonnes supplémentaires. Notez également que a des valeurs non nulles et que les autres nouvelles colonnes ont des valeurs nulles. 按 hostid 分组

Étape 3 : Regroupez et agrégez la table étendue

. Nous avons besoin de car il fournit la valeur y :

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
);

select * from history_itemvalue_pivot;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 | NULL |
|      2 |    9 | NULL |   40 |
+--------+------+------+------+

NULL (Notez que nous avons maintenant une ligne par valeur y.)

D'accord, nous y sommes presque ! Il faut juste se débarrasser des moches .

Étape 4 : Embellir

. Nous remplacerons toutes les valeurs nulles par des zéros pour que l'ensemble de résultats soit meilleur :

---

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;

+--------+------+------+------+
| hostid | A    | B    | C    |
+--------+------+------+------+
|      1 |   10 |    3 |    0 |
|      2 |    9 |    0 |   40 |
+--------+------+------+------+

Nous avons terminé : nous avons créé un magnifique tableau croisé dynamique en utilisant MySQL.

🎜 🎜Choses à noter lors de l'application de ce processus :🎜

• Quelles valeurs utiliser dans les colonnes supplémentaires. J'ai utilisé itemvalue
dans cet exemple
• Quelles valeurs "neutres" utiliser dans les colonnes supplémentaires. J'ai utilisé NULL，但也可能是 0 或 "", cela dépend de votre situation spécifique
• Quelle fonction d'agrégation utiliser lors du regroupement. J'ai utilisé sum，但是 count 和 max 也经常使用（max souvent utilisé lors de la construction d'une seule ligne) "objet" réparti sur plusieurs lignes)
• Utilisez plusieurs colonnes pour représenter les valeurs y. Cette solution ne se limite pas à utiliser une seule colonne pour les valeurs y - insérez simplement les colonnes supplémentaires group by 子句（并且不要忘记 selectelles)

Limites connues :

• Cette solution n'autorise pas n colonnes dans le tableau croisé dynamique - chaque colonne pivot doit être ajoutée manuellement lors de l'extension du tableau de base. Donc pour des valeurs de 5 ou 10 x, cette solution convient. 100 yuans, pas très bien. Il existe des solutions utilisant des procédures stockées pour générer des requêtes, mais elles sont laides et difficiles à réaliser correctement. Je ne connais actuellement aucun bon moyen de résoudre ce problème lorsque le tableau croisé dynamique doit comporter plusieurs colonnes.