MySQL draait rij naar dynamisch aantal kolommen

Laten we zeggen dat ik drie verschillende MySQL-tabellen heb:

Tabel products:

id | name
 1   Product A
 2   Product B

Tabel partners:

id | name
 1   Partner A
 2   Partner B

Tabel 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

Ik wil graag een tabel met partners in de rijen en producten als kolommen. Tot nu toe was ik in staat om een ​​output als deze te krijgen:

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

Met deze zoekopdracht:

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

maar ik zou in plaats daarvan iets willen hebben als:

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

Het probleem is dat ik niet kan zeggen hoeveel producten ik zal hebben, dus het kolomnummer moet dynamisch veranderen, afhankelijk van de rijen in de producttabel.

Dit zeer goede antwoord lijkt niet te werken met mysql: T-SQL Pivot? Mogelijkheid om tabelkolommen te maken van rijwaarden

Antwoord 1, autoriteit 100%

Helaas heeft MySQL geen functie PIVOT, wat u eigenlijk probeert te doen. U moet dus een aggregatiefunctie gebruiken met een CASE-instructie:

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

Bekijk SQL-demo

Omdat u de Producten niet kent, wilt u dit waarschijnlijk dynamisch uitvoeren. Dit kan worden gedaan met behulp van voorbereide verklaringen.

Met dynamische draaitabellen (transformeer rijen naar kolommen) ziet uw code er als volgt uit:

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;

Bekijk SQL-demo

Het is waarschijnlijk vermeldenswaard dat GROUP_CONCATstandaard beperkt is tot 1024 bytes. U kunt dit omzeilen door het hoger in te stellen voor de duur van uw procedure, dwz. SET @@group_concat_max_len = 32000;