Wie konvertiere ich MySQL-Zeilen in Spalten basierend auf mehreren Spalten?

MySQL-Abfrage zum Konvertieren von Zeilen in Spalten basierend auf mehreren Spalten

Einführung:

Dynamisch Das Konvertieren von Zeilen in Spalten ist eine häufige Aufgabe bei der Datenbearbeitung. In diesem Artikel untersuchen wir eine Lösung, die eine MySQL-Abfrage verwendet und die Funktionen einer früheren Lösung erweitert, die eine Einzelspaltenkonvertierung abwickelte.

Problem:

Sie haben eine Tabelle mit mehreren Zeilen, die Daten und entsprechende Preise darstellen, geordnet nach Bestellung und Artikel. Das Ziel besteht darin, diese Daten in eine Tabelle umzuwandeln, in der jede Bestellung als Spalte und jeder Artikel als Zeile dargestellt wird, wobei die entsprechenden Preise angezeigt werden.

Lösung:

Um diese mehrspaltige Konvertierung zu erreichen, wenden wir einen zweistufigen Prozess an.

Schritt 1: Entpivotieren der Daten

Wir beginnen mit der Entpivotierung der Daten mithilfe von UNION ALL, um mehrere Zeilen für jede Kombination aus Bestellung und Artikel zu erstellen. Dies ermöglicht es uns, die Daten im nächsten Schritt einfacher zu manipulieren.

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

Schritt 2: Pivotieren der entpivotierten Daten

Sobald die Daten entpivotiert sind, führen wir eine Pivotierung durch es mithilfe von Aggregatfunktionen mit CASE-Anweisungen wieder in Spalten zurück. Dadurch werden die nicht geschwenkten Zeilen nach ID gruppiert und die entsprechenden Werte den entsprechenden Spalten zugewiesen.

SELECT
  id, 
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

Dynamische Abfragegenerierung:

Um Tabellen mit unterschiedlicher Anzahl von Bestellungen zu verarbeiten und Elemente können wir die Abfrage mithilfe einer vorbereiteten Abfrage dynamisch generieren Anweisung.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `', 
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

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

Ergebnis:

Die resultierende Tabelle bietet eine prägnante Darstellung der Daten, wobei jede Bestellung als Spalte und jedes Element als Zeile angezeigt wird die entsprechenden Preise.

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