Requête de tableau croisé dynamique MySQL : utilisation de colonnes dynamiques pour l'analyse des données

Question

J'utilise le tableau suivant pour stocker les données produit :

mysql> SELECT * FROM produit ;
+--------------+---------------+--------+
| identifiant nom | description |
+--------------+---------------+--------+
| 1 | produit1 | premier produit |
| 2 | produit2 | deuxième produit | 5 |
+--------------+---------------+--------+

mysql> SELECT * FROM product_additional;
+-----------------+----------------+
| identifiant | nom du champ |
+-----------------+----------------+
| 1 |
| 1 | hauteur | 103 |
| 2 |
| 2 | hauteur 13 |
| 2 | couleur noir |
+-----------------+----------------+

Utilisez la requête suivante pour sélectionner des enregistrements dans les deux tables :

mysql>
    p.id
    ,p.nom
    , p.description
    ,MAX(IF(pa.fieldname = 'size', pa.fieldvalue, NULL)) comme `size`
    ,MAX(IF(pa.fieldname = 'height', pa.fieldvalue, NULL)) comme `hauteur`
    ,MAX(IF(pa.fieldname = 'color', pa.fieldvalue, NULL)) comme `color`
DE produit p
LEFT JOIN product_additional AS pa ON p.id = pa.id
GROUPER PAR p.id
+--------------+---------------+--------+-------- -+--------+
| identifiant nom | description | taille |
+--------------+---------------+--------+-------- -+--------+
| 1 | produit1 | premier produit |
| 2 | produit2 | deuxième produit | L |
+--------------+---------------+--------+-------- -+--------+
Tout fonctionne bien :) 
Puisque je remplis la table "supplémentaire" de manière dynamique, ce serait bien si la requête était également dynamique. De cette façon, je n'ai pas besoin de modifier la requête à chaque fois que j'entre un nouveau nom de champ et une nouvelle valeur de champ. 

Answer 1

J'ai une manière légèrement différente d'y parvenir que la réponse acceptée. Cette approche évite d'utiliser GROUP_CONCAT qui a une limite de 1024 caractères par défaut et ne fonctionnera pas si vous avez beaucoup de champs à moins que vous ne modifiiez la limite.

SET @sql = '';
SELECT
    @sql := CONCAT(@sql,if(@sql='','',', '),temp.output)
FROM
(
    SELECT
      DISTINCT
        CONCAT(
         'MAX(IF(pa.fieldname = ''',
          fieldname,
          ''', pa.fieldvalue, NULL)) AS ',
          fieldname
        ) as output
    FROM
        product_additional
) as temp;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Cliquez ici pour voir la démo

Answer 2

Dans MySQL, la seule façon de le faire de manière dynamique est d'utiliser des instructions préparées. Voici un bon article à leur sujet :

Tableau croisé dynamique (convertir les lignes en colonnes)

Votre code ressemblera à ceci :

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(pa.fieldname = ''',
      fieldname,
      ''', pa.fieldvalue, NULL)) AS ',
      fieldname
    )
  ) INTO @sql
FROM product_additional;

SET @sql = CONCAT('SELECT p.id
                    , p.name
                    , p.description, ', @sql, ' 
                   FROM product p
                   LEFT JOIN product_additional AS pa 
                    ON p.id = pa.id
                   GROUP BY p.id, p.name, p.description');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Voir Démo

Remarque : La limite de caractères de la fonction GROUP_CONCAT est de 1024 caractères. Voir le paramètre group_concat_max_len