Wie entpivotiere ich Spalten dynamisch in Zeilen in MySQL?

Dynamische Spalten in Zeilen umwandeln

Das Umwandeln von Spalten in Zeilen, oft auch als „Unpivoting“ bezeichnet, kann eine wertvolle Technik bei der Datenumformung sein . Durch die Konvertierung breiter Tabellen mit mehreren Spalten in schmale Tabellen mit weniger Spalten wird es einfacher, die Daten zu analysieren und mit ihnen zu arbeiten.

Dynamisches Unpivoting in MySQL

In MySQL Es gibt keine integrierte UNPIVOT-Funktion. Sie können die Pivotierung jedoch mit einer Kombination aus SQL-Anweisungen und dynamischer SQL-Generierung erreichen.

Beispiel: Tabelle_1 entpivotieren

Betrachten Sie das folgende Beispiel, in dem wir die Pivotierung von Tabelle_1 aufheben möchten in Expected_Result_Table, wobei nur Werte größer als berücksichtigt werden 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

Dynamische SQL-Generierung zum Entpivotieren

Um Tabelle_1 zu entpivotieren, müssen wir zunächst eine dynamische SQL-Anweisung basierend auf den Spaltennamen von generieren Tabelle_1. Dies kann mit den folgenden Schritten erreicht werden:

1. Generieren Sie eine Liste eindeutiger Spaltennamen mit Ausnahme der Spalte „ID“ mithilfe der folgenden Abfrage:

   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. Verketten Sie die generierten SQL-Strings, um das endgültige dynamische SQL zu bilden Anweisung:

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

3. Bereiten Sie die dynamische SQL-Anweisung vor und führen Sie sie mit dem folgenden Code aus:

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

Ergebnis:

Das Ausführen der dynamischen SQL-Anweisung generiert die 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

Das obige ist der detaillierte Inhalt vonWie entpivotiere ich Spalten dynamisch in Zeilen in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!