Wie generiere ich dynamisch Spalten für Zeilen-Säulen-Pivoting in MySQL?

MySQL-Zeilen-zu-Spalte dynamische Spaltengenerierung

Angenommen, es gibt drei MySQL-Tabellen:

• Produkttabelle: speichert Produktinformationen (ID, Name).
• Partnertabelle: speichert Partnerinformationen (ID, Name).
• Verkaufstabelle: zeichnet Verkaufstransaktionen auf (Partner_ID, Produkt_ID).

Unser Ziel ist es, die Verkaufsdaten in eine Tabelle umzuwandeln, in der Partner als Zeilen und Produkte als dynamische Spalten aufgeführt sind.

Ergebnisse vor der Aggregation

Mit einer einfachen Abfrage können wir eine Tabelle mit Partnernamen und Produktnamen als Spalten sowie der Häufigkeit des Vorkommens erhalten:

SELECT partners.name AS partner_name, 
       products.name AS product_name, 
       COUNT(*) 
FROM sales
JOIN products ON sales.products_id = products.id
JOIN partners ON sales.partners_id = partners.id
GROUP BY sales.partners_id, sales.products_id

Diesem Ansatz fehlt jedoch die dynamische Spaltengenerierung für variable Produktmengen.

Dynamisches Pivotieren mit CASE und Aggregatfunktionen

MySQL verfügt nicht über eine dedizierte PIVOT-Funktion, daher müssen wir die CASE-Anweisung für Aggregatabfragen verwenden:

SELECT pt.partner_name,
  COUNT(CASE WHEN pd.product_name = 'Product A' THEN 1 END) AS ProductA,
  COUNT(CASE WHEN pd.product_name = 'Product B' THEN 1 END) AS ProductB,
  COUNT(CASE WHEN pd.product_name = 'Product C' THEN 1 END) AS ProductC,
  COUNT(CASE WHEN pd.product_name = 'Product D' THEN 1 END) AS ProductD,
  COUNT(CASE WHEN pd.product_name = 'Product E' THEN 1 END) AS ProductE
FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name

Diese Methode kann die Anzahl der Vorkommen jedes Produktnamens für jeden Partner dynamisch zählen.

Dynamische Perspektive mit vorbereiteten Aussagen

Für eine wirklich dynamische Pivot-Tabelle können wir vorbereitete Anweisungen verwenden, um SQL-Abfragezeichenfolgen basierend auf der Anzahl der Produkte zu generieren:

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'COUNT(CASE WHEN Product_Name = ''',
      Product_Name,
      ''' THEN 1 END) AS ',
      REPLACE(Product_Name, ' ', '')
    )
  ) INTO @sql
FROM products;

SET @sql = CONCAT('SELECT pt.partner_name, ', @sql, ' FROM partners pt
LEFT JOIN sales s ON pt.part_id = s.partner_id
LEFT JOIN products pd ON s.product_id = pd.prod_id
GROUP BY pt.partner_name');

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

Mit dieser Methode können wir Spaltennamen und Gesamtzahlen für jedes Produkt dynamisch generieren. Denken Sie daran, die Größenbeschränkung von GROUP_CONCAT bei Bedarf anzupassen.

Das obige ist der detaillierte Inhalt vonWie generiere ich dynamisch Spalten für Zeilen-Säulen-Pivoting in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!