Comment annuler le pivotement de colonnes dynamiques en lignes dans MySQL ?

Création de lignes à partir de colonnes dynamiques

Dans le domaine de la gestion des données, la manipulation des colonnes et des lignes est souvent nécessaire. Cette question se penche sur une tâche spécifique : convertir plusieurs colonnes dynamiques en un nouveau tableau avec des lignes contenant des critères spécifiques.

L'exemple donné illustre l'arrêt du pivotement de Table_1, ce qui donne Expected_Result_Table. La principale différence est que Expected_Result_Table affiche chaque valeur non nulle dans une ligne distincte.

MySQL ne dispose pas d'une fonction UNPIVOT directe, mais cette limitation peut être surmontée à l'aide d'une instruction UNION ALL. L'approche de base consiste à créer des instructions SELECT distinctes pour chaque colonne, en sélectionnant l'ID, le nom de la colonne comme valeur « mot » et la valeur de la colonne comme valeur « quantité ». Ces instructions SELECT sont ensuite combinées à l'aide de UNION ALL.

Par exemple :

SELECT id, 'abc' AS word, abc AS qty
FROM yt
WHERE abc > 0
UNION ALL
SELECT id, 'brt', brt
FROM yt
WHERE brt > 0

Cette approche devient plus complexe lorsqu'il s'agit de colonnes dynamiques. Pour résoudre ce problème, des déclarations préparées peuvent être utilisées. Ils permettent la génération dynamique d'instructions SQL basées sur des métadonnées.

L'extrait de code suivant illustre une telle approche :

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
WHERE c.table_name = 'yt'
  AND c.column_name NOT IN ('id')
ORDER BY c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');


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

Dans cet exemple, le SQL dynamique est construit à l'aide des informations du table information_schema.columns. L'instruction préparée stmt est ensuite utilisée pour exécuter le SQL généré dynamiquement, annulant ainsi le pivotement des colonnes dynamiques en lignes.

En mettant en œuvre cette approche, il est possible de transformer des colonnes dynamiques en lignes, répondant à des besoins de données spécifiques.

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!