Comment faire pivoter dynamiquement des tableaux croisés dynamiques dans MySQL ?

Pivot dynamique MySQL : convertir les lignes en colonnes

Dans MySQL, vous aurez peut-être besoin d'un tableau croisé dynamique pour convertir les lignes en colonnes. Ceci est particulièrement utile lorsque vous devez représenter des données dans différentes orientations. Bien que MySQL ne fournisse pas nativement de fonction PIVOT, il existe plusieurs façons d'accomplir cette tâche.

Défi : tableau croisé dynamique dynamique

Supposons que vous ayez trois tableaux : Produits, Partenaires et Ventes. Vous devez créer un tableau avec les partenaires sous forme de lignes et les produits sous forme de colonnes, et calculer la quantité de ventes associée à chaque combinaison.

Utiliser l'instruction CASE

Une solution consiste à utiliser une fonction d'agrégation avec une instruction CASE. En créant plusieurs relevés CASE, vous pouvez calculer les ventes pour chaque produit et regrouper les résultats par nom de partenaire.

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;

Utilisez des instructions préparées pour implémenter une perspective dynamique

Pour les situations où le nombre de produits est inconnu et change dynamiquement, vous pouvez utiliser des instructions préparées. En générant dynamiquement des requêtes SQL, vous pouvez ajuster le nombre de colonnes en fonction des produits disponibles.

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;

Restrictions et remarques

Notez que GROUP_CONCAT la limite d'octets par défaut est de 1024. Si la chaîne concaténée dépasse cette limite, vous devrez peut-être utiliser SET @@group_concat_max_len = 32000; pour augmenter la limite.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!