MySQL - Rows to Columns

Question

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

J'ai une table (appelée historique) avec 3 colonnes : hostid, itemname, itemvalue.
Si je fais une sélection ( select * from history ), il retournera

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

Comment puis-je interroger la base de données pour obtenir quelque chose comme

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

Answer 1

Je vais ajouter une explication un peu plus longue et plus détaillée des étapes à suivre 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 pour le reste de ce billet. Ce sera le tableau de base :

select * from history;

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

Ce sera notre objectif, le joli tableau croisé dynamique :

select * from history_itemvalue_pivot;

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

Valeurs dans la history.hostid La colonne deviendra valeurs y dans le tableau croisé dynamique. Les valeurs dans le history.itemname La colonne deviendra valeurs x (pour des raisons évidentes).

---

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

1. sélectionner les colonnes qui nous intéressent, c'est-à-dire valeurs y et valeurs x
2. étendre la table de base avec des colonnes supplémentaires -- une pour chaque valeur x
3. groupe et agréger la table étendue -- un groupe pour chaque valeur y
4. (facultatif) prétendre le tableau agrégé

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

Étape 1 : sélectionner les colonnes qui vous intéressent . Dans le résultat souhaité, hostid fournit le valeurs y et itemname fournit le valeurs x .

Étape 2 : étendre le tableau de base avec des colonnes supplémentaires . Nous avons généralement besoin d'une colonne par valeur x. Rappelez-vous que notre colonne de valeurs x est itemname :

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 le modèle de NULL s -- une rangée avec itemname = "A" a une valeur non nulle pour la nouvelle colonne A et des valeurs nulles pour les autres nouvelles colonnes.

Étape 3 : regrouper et agréger la table étendue . Nous devons group by hostid puisqu'il fournit les valeurs 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 |
+--------+------+------+------+

(Notez que nous avons maintenant une ligne par valeur y). Ok, on y est presque ! Nous avons juste besoin de nous débarrasser de ces affreux NULL s.

Étape 4 : embellir . Nous allons simplement remplacer toutes les valeurs nulles par des zéros afin que l'ensemble des résultats soit plus agréable à regarder :

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 |
+--------+------+------+------+

Et nous avons terminé -- nous avons construit un joli tableau croisé dynamique en utilisant MySQL.

---

Considérations lors de l'application de cette procédure :

• quelle valeur utiliser dans les colonnes supplémentaires. J'ai utilisé itemvalue dans cet exemple
• quelle valeur "neutre" utiliser dans les colonnes supplémentaires. J'ai utilisé NULL mais cela pourrait aussi être 0 o "" en fonction de votre situation exacte
• quelle fonction d'agrégation utiliser pour le regroupement. J'ai utilisé sum mais count et max sont également souvent utilisés ( max est souvent utilisé pour construire des "objets" d'une seule rangée qui étaient répartis sur plusieurs rangées.)
• en utilisant plusieurs colonnes pour les valeurs y. Cette solution n'est pas limitée à l'utilisation d'une seule colonne pour les valeurs en ordonnée - il suffit d'insérer les colonnes supplémentaires dans le champ group by (et n'oubliez pas de select les)

Limites connues :

• cette solution ne permet pas d'avoir n colonnes dans le tableau croisé dynamique -- chaque colonne croisée dynamique doit être ajoutée manuellement lors de l'extension du tableau de base. Ainsi, pour 5 ou 10 valeurs x, cette solution est intéressante. Pour 100, elle ne l'est pas. Il existe des solutions avec des procédures stockées générant une requête, mais elles sont laides et difficiles à mettre en œuvre. Je ne connais pas actuellement de bonne façon de résoudre ce problème lorsque le tableau croisé dynamique doit avoir beaucoup de colonnes.

Answer 2

SELECT 
    hostid, 
    sum( if( itemname = 'A', itemvalue, 0 ) ) AS A,  
    sum( if( itemname = 'B', itemvalue, 0 ) ) AS B, 
    sum( if( itemname = 'C', itemvalue, 0 ) ) AS C 
FROM 
    bob 
GROUP BY 
    hostid;

Answer 3

En profitant de l'idée de Matt Fenwick qui m'a aidé à résoudre le problème (un grand merci), réduisons-le à une seule requête :

select
    history.*,
    coalesce(sum(case when itemname = "A" then itemvalue end), 0) as A,
    coalesce(sum(case when itemname = "B" then itemvalue end), 0) as B,
    coalesce(sum(case when itemname = "C" then itemvalue end), 0) as C
from history
group by hostid

Answer 4

J'ai trouvé ce post

Transformer des lignes en colonnes, dans une vue. Est-ce possible ?

J'espère que cela vous aidera.

Answer 5

Utiliser une sous-requête

SELECT  hostid, 
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='A' AND hostid = t1.hostid) AS A,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='B' AND hostid = t1.hostid) AS B,
    (SELECT VALUE FROM TableTest WHERE ITEMNAME='C' AND hostid = t1.hostid) AS C
FROM TableTest AS T1
GROUP BY hostid

mais cela posera un problème si la sous-requête résulte de plus d'une ligne, utilisez une autre fonction d'agrégation dans la sous-requête.