Transformer une ligne MySQL en un nombre dynamique de colonnes

Question

Disons que j'ai trois tables MySQL différentes :

Table products:

id | name
 1   Product A
 2   Product B

Table partners:

id | name
 1   Partenaire A
 2   Partenaire B

Table 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

J'aimerais obtenir un tableau avec les partenaires dans les lignes et les produits en tant que colonnes. Jusqu'à présent, j'ai pu obtenir une sortie comme ceci :

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

En utilisant cette requête :

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

mais j'aimerais plutôt obtenir quelque chose comme :

nom_partenaire | Product A | Product B | Product C | Product D | Product E
Partenaire A              1           1           1           1           2
Partenaire B              0           1           1           1           1

Le problème est que je ne peux pas savoir combien de produits j'aurai donc le nombre de colonnes doit changer dynamiquement en fonction des lignes dans la table des produits.

Cette très bonne réponse ne semble pas fonctionner avec mysql : T-SQL Pivot? Possibility of creating table columns from row values

Answer 1

Malheureusement, MySQL n'a pas de fonction de PIVOT qui est essentiellement ce que vous essayez de faire. Vous devrez donc utiliser une fonction d'agrégation avec une instruction CASE:

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

See Démo SQL

Étant donné que vous ne connaissez pas les produits, vous voudrez probablement effectuer cette action de manière dynamique. Cela peut être fait en utilisant des déclarations préparées.

Avec des tables pivot dynamiques (transformer les lignes en colonnes) votre code ressemblerait à ceci:

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;

See Démo SQL

Il est probablement utile de noter que GROUP_CONCAT est par défaut limité à 1024 octets. Vous pouvez contourner cela en le configurant plus grand pour la durée de votre procédure, c'est-à-dire SET @@group_concat_max_len = 32000;