Comment effectuer un pivotement dynamique dans MySQL à l'aide d'instructions préparées ?

Pivot dynamique dans MySQL

Dans MySQL, les tables contiennent souvent des données dans un format normalisé, nécessitant des requêtes complexes pour récupérer les données dans un format plus utilisateur -format convivial. L'une de ces transformations est le pivotement, où les colonnes sont converties en lignes.

Considérons un tableau tel que « Parts », qui relie les parties du produit (part_id) aux types de produits (part_type) et aux ID de produit (product_id). Nous souhaitons faire pivoter ce tableau pour créer un nouveau tableau affichant les identifiants de pièces de chaque produit pour différents types de pièces.

MySQL ne dispose pas d'une fonction PIVOT dédiée, nous devons donc utiliser des techniques alternatives. Une solution consiste à utiliser des fonctions d'agrégation et des instructions CASE. Pour le pivotement dynamique, nous pouvons exploiter les instructions préparées.

Requête de pivotement dynamique

La requête suivante utilise du SQL dynamique pour faire pivoter la table « Parts » :

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'max(case when part_type = ''',
      part_type,
      ''' then part_id end) AS part_',
      part_type, '_id'
    )
  ) INTO @sql
FROM
  parts;
SET @sql = CONCAT('SELECT product_id, ', @sql, ' 
                  FROM parts 
                   GROUP BY product_id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Cette requête génère dynamiquement une instruction SQL basée sur les types de pièces distincts présents dans la table et l'exécute à l'aide d'instructions préparées pour une amélioration performances.

Exemple de résultat

Le tableau résultant aura le format suivant :

product_id      part_A_id      part_B_id
----------------------------------------
1               1              2
2               3              4
3               5              6

Requête de pivot statique

Pour un nombre limité de types de pièces, une requête pivot statique peut être utilisé :

select product_id,
  max(case when part_type ='A' then part_id end) as Part_A_Id,
  max(case when part_type ='B' then part_id end) as Part_B_Id
from parts
group by product_id

Cette requête spécifie manuellement les types de pièces et génère le tableau croisé dynamique.

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!