Comment dépivoter dynamiquement des colonnes en lignes dans MySQL ?

Transposition de colonnes dynamiques en lignes

La transposition de colonnes en lignes, souvent appelée « non pivotante », peut être une technique précieuse dans la refonte des données. . En convertissant des tableaux larges avec plusieurs colonnes en tableaux étroits avec moins de colonnes, il devient plus facile d'analyser et de travailler avec les données.

Unpivoting dynamique dans MySQL

Dans MySQL, il n'y a pas de fonction UNPIVOT intégrée. Cependant, vous pouvez obtenir l'arrêt du pivotement en utilisant une combinaison d'instructions SQL et de génération SQL dynamique.

Exemple : Table non pivotante_1

Considérez l'exemple suivant, où nous souhaitons annuler le pivotement de la Table_1 dans Expected_Result_Table, en considérant uniquement les valeurs supérieures à 0 :

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

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

Génération SQL dynamique pour annuler le pivotement

Pour annuler le pivotement de la Table_1, nous devons d'abord générer une instruction SQL dynamique basée sur les noms de colonnes de Tableau_1. Ceci peut être réalisé en suivant les étapes suivantes :

1. Générez une liste de noms de colonnes distincts à l'exclusion de la colonne "Id" à l'aide de la requête suivante :

   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;

2. Concaténez les chaînes SQL générées pour former le SQL dynamique final instruction :

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

3. Préparez et exécutez l'instruction SQL dynamique en utilisant le code suivant :

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

Résultat :

L'exécution de l'instruction SQL dynamique générera le Attended_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

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!