MySQL pivoter la ligne en nombre dynamic de colonnes

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

products table:

 id | name 1 Product A 2 Product B 

Table partners :

 id | name 1 Partner A 2 Partner B 

sales table:

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

Je voudrais obtenir une table avec des partenaires dans les lignes et des produits sous forme de colonnes. Jusqu’à présent, j’ai pu obtenir un résultat comme celui-ci:

 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 

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 avoir quelque chose comme:

 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 

Le problème est que je ne peux pas dire combien de produits j’aurai, alors le numéro de colonne doit changer dynamicment en fonction des lignes de la table des produits.

Cette très bonne réponse ne semble pas fonctionner avec mysql: T-SQL Pivot? Possibilité de créer des colonnes de table à partir de valeurs de ligne

Malheureusement, MySQL n’a pas de fonction PIVOT , ce 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 

Voir SQL Fiddle with Demo

Comme vous ne connaissez pas les produits, vous voudrez probablement effectuer cette opération de manière dynamic. Cela peut être fait en utilisant des instructions préparées.

Tableaux pivot dynamics (transformation de 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; 

Voir SQL Fiddle with Demo

Il est probablement intéressant de noter que GROUP_CONCAT est par défaut limité à 1024 octets. Vous pouvez contourner ce problème en le réglant plus haut pour la durée de votre procédure, c.-à-d. SET @@group_concat_max_len = 32000;