Wie kann ich Daten mit dynamischen Spalten in MySQL entpivotieren, wenn Ihnen eine UNPIVOT-Funktion fehlt?

Entpivotieren von Daten mit dynamischen Spalten

Im Bereich der Abfragetransformation spielt das Entpivotieren von Daten eine entscheidende Rolle bei der Umgestaltung von Tabellenstrukturen. Ein solches Szenario beinhaltet die Transponierung dynamischer Spalten in Zeilen, eine Aufgabe, die durch das Fehlen einer UNPIVOT-Funktion in MySQL behindert werden kann.

Problem: Transponieren dynamischer Spalten

Stellen Sie sich eine Tabelle namens Table_1 vor mit dynamischen Spalten (abc, brt, ccc usw.), die verschiedene Attribute darstellen, wie gezeigt unten:

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

Das Ziel besteht darin, Table_1 in eine Expected_Result_Table umzuwandeln, die die Spalten als Zeilen auflistet, mit nur Werten ungleich Null:

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

Lösung: Dynamic UNION ALL und Prepared Statement

Um diese Transformation zu erreichen, können wir eine Kombination aus UNION ALL und einem Prepared nutzen Anweisung zur Verarbeitung dynamischer Spalten:

1. Dynamisches SQL generieren: Mithilfe von information_schema.columns können wir dynamisches SQL generieren, das Zeilen für jede Spalte ungleich Null auswählt.
2. Prepare-Anweisung: Wir verwenden eine vorbereitete Anweisung, um das dynamische SQL auszuführen und eine mögliche SQL-Injection zu vermeiden Schwachstellen.
3. Anweisung ausführen: Wir führen die vorbereitete Anweisung aus, um die nicht gepivotierten Daten abzurufen.

Hier ist der Code für die dynamische Lösung:

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

Ergebnis:

Die resultierende Tabelle enthält die gewünschten Zeilen:

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 kann ich Daten mit dynamischen Spalten in MySQL entpivotieren, wenn Ihnen eine UNPIVOT-Funktion fehlt?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!