Heim >Datenbank >MySQL-Tutorial >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:
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:
<code class="language-sql">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</code>
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:
<code class="language-sql">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</code>
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:
<code class="language-sql">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;</code>
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!