MySQL pivot-Zeile in einer dynamischen Anzahl von Spalten

Können sagen, ich habe drei verschiedene MySQL-Tabellen:

Tabelle products:

id | name
 1   Product A
 2   Product B

Tabelle partners:

id | name
 1   Partner A
 2   Partner B

Tabelle sales:

partners_id | products_id
          1             2
          2             5
          1             5
          1             3
          1             4
          1             5
          2             2
          2             4
          2             3
          1             1

Ich würde gerne eine Tabelle mit Partnern in den Reihen und Produkte, die als Spalten. Bisher war ich in der Lage, zu erhalten eine Ausgabe wie diese:

name      | name      | COUNT( * )
Partner A   Product A          1
Partner A   Product B          1
Partner A   Product C          1
Partner A   Product D          1
Partner A   Product E          2
Partner B   Product B          1
Partner B   Product C          1
Partner B   Product D          1
Partner B   Product E          1

Mithilfe dieser Abfrage:

SELECT partners.name, products.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
LIMIT 0 , 30

aber ich möchte stattdessen etwas wie:

partner_name | Product A | Product B | Product C | Product D | Product E
Partner A              1           1           1           1           2
Partner B              0           1           1           1           1

Das problem ist, dass ich nicht sagen kann, wie viele Produkte habe ich also die Spalte Nummer muss dynamisch ändern, je nachdem die Reihen in die Tabelle Produkte.

Diese sehr gute Antwort nicht scheinen, um mit mysql zu arbeiten: T-SQL Pivot? Möglichkeit der Erstellung von Tabellen-Spalten aus der Zeile Werte

Siehe link Zeile zu Spalte auf für mehrere Vorschläge.
Dynamic-generator

InformationsquelleAutor FeeJai | 2012-08-17

Leider MySQL nicht über eine PIVOT Funktion, die im Grunde, was Sie zu tun versuchen. So benötigen Sie eine Aggregatfunktion mit einem CASE Aussage:

select pt.partner_name,
  count(case when pd.product_name = 'Product A' THEN 1 END) ProductA,
  count(case when pd.product_name = 'Product B' THEN 1 END) ProductB,
  count(case when pd.product_name = 'Product C' THEN 1 END) ProductC,
  count(case when pd.product_name = 'Product D' THEN 1 END) ProductD,
  count(case when pd.product_name = 'Product E' THEN 1 END) 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

Sehen SQL Fiddle mit Demo

Da Sie nicht wissen, die Produkte, die Sie werden wahrscheinlich möchten Sie dieses dynamisch. Diese kann getan werden, mit prepared statements.

Dynamische pivot-Tabellen (Transformation der Zeilen, Spalten)

Dein code würde wie folgt Aussehen:

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;

Sehen SQL Fiddle mit Demo

Es ist wohl erwähnenswert, dass GROUP_CONCAT ist standardmäßig begrenzt auf 1024 Byte. Sie können das umgehen, indem man es höher für die Dauer des Verfahren, dh. SET @@group_concat_max_len = 32000;

Der Kommentar am Ende bezüglich der "max_len" wurde ein Lebensretter! Danke für den Tipp.
wenn Sie sind auf der Suche nach mehr dynamischen pivot-Abfragen, überprüfen Sie bitte: boynux.com/creating-pivot-reports-in-mysql
Was passiert, wenn der name des Produkts ist ProductA') from partners pt; truncate partners;

InformationsquelleAutor Taryn

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.