Comment générer dynamiquement des colonnes pour le pivot de ligne à colonne dans MySQL?

Génération dynamique de colonnes MySQL ligne à colonne

Supposons qu'il y ait trois tables MySQL :

• table des produits : stocke les informations sur le produit (identifiant, nom).
• table des partenaires : stocke les informations sur les partenaires (identifiant, nom).
• table des ventes : enregistre les transactions de vente (partners_id, products_id).

Notre objectif est de convertir les données de ventes en un tableau avec les partenaires répertoriés sous forme de lignes et les produits sous forme de colonnes dynamiques.

Résultats de pré-agrégation

À l'aide d'une simple requête, nous pouvons obtenir un tableau avec le nom du partenaire et le nom du produit sous forme de colonnes, ainsi que le nombre d'occurrences :

<code class="language-sql">SELECT partners.name AS partner_name, 
       products.name AS product_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</code>

Cependant, cette approche manque de génération dynamique de colonnes pour des quantités de produits variables.

Pivotement dynamique à l'aide de CASE et de fonctions d'agrégation

MySQL n'a pas de fonction PIVOT dédiée, nous devons donc utiliser l'instruction CASE pour les requêtes agrégées :

<code class="language-sql">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</code>

Cette méthode permet de compter dynamiquement le nombre d'occurrences de chaque nom de produit pour chaque partenaire.

Perspective dynamique utilisant des déclarations préparées

Pour un tableau croisé dynamique véritablement dynamique, nous pouvons utiliser des instructions préparées pour générer des chaînes de requête SQL basées sur le nombre de produits :

<code class="language-sql">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;</code>

Cette méthode nous permet de générer dynamiquement des noms de colonnes et des décomptes agrégés pour chaque produit. N'oubliez pas d'ajuster la limite de taille de GROUP_CONCAT si nécessaire.

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!