Comment annuler le pivotement des données avec des colonnes dynamiques dans MySQL lorsqu'il vous manque une fonction UNPIVOT ?

Données non pivotantes avec colonnes dynamiques

Dans le domaine de la transformation des requêtes, les données non pivotantes jouent un rôle crucial dans la refonte des structures de table. L'un de ces scénarios consiste à transposer des colonnes dynamiques en lignes, une tâche que l'absence de fonction UNPIVOT dans MySQL peut entraver.

Problème : transposer des colonnes dynamiques

Imaginez une table appelée Table_1 avec des colonnes dynamiques (abc, brt, ccc, etc.) représentant différents attributs, comme indiqué ci-dessous :

Table_1
-----------------------------------------
Id       abc  brt ccc ddq eee fff gga hxx
-----------------------------------------
12345     0    1   0   5   0   2   0   0  
21321     0    0   0   0   0   0   0   0   
33333     2    0   0   0   0   0   0   0   
41414     0    0   0   0   5   0   0   1   
55001     0    0   0   0   0   0   0   2   
60000     0    0   0   0   0   0   0   0 
77777     9    0   3   0   0   0   0   0

Le but est de transformer Table_1 en Expected_Result_Table qui répertorie les colonnes sous forme de lignes, avec uniquement des valeurs non nulles :

Expected_Result_Table
---------------------
Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

Solution : Dynamic UNION ALL et déclaration préparée

Pour accomplir cette transformation, nous pouvons tirer parti d'une combinaison d'UNION ALL et d'une déclaration préparée instruction pour gérer les colonnes dynamiques :

1. Générer du SQL dynamique : En utilisant information_schema.columns, nous pouvons générer du SQL dynamique qui sélectionne des lignes pour chaque colonne non nulle.
2. Instruction Préparer : Nous utilisons une instruction préparée pour exécuter le SQL dynamique et éviter une éventuelle injection SQL vulnérabilités.
3. Execute Statement : Nous exécutons l'instruction préparée pour récupérer les données non pivotées.

Voici le code de la solution dynamique :

-- Generate dynamic SQL
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;

-- Build the final SQL query
SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

-- Prepare and execute the statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Résultat :

Le tableau résultant sera contenir les lignes souhaitées :

Id      Word   Qty>0
---------------------
12345    brt    1
12345    ddq    5
12345    fff    2
33333    abc    2
41414    eee    5
41414    hxx    1
55001    hxx    2
77777    abc    9
77777    ccc    3

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!