Comment convertir des lignes MySQL en colonnes basées sur plusieurs colonnes ?

Requête MySQL pour convertir des lignes en colonnes en fonction de plusieurs colonnes

Introduction :

Dynamiquement la conversion de lignes en colonnes est une tâche courante dans la manipulation de données. Dans cet article, nous explorons une solution utilisant une requête MySQL, étendant les capacités d'une solution précédente qui gérait une conversion d'une seule colonne.

Problème :

Vous avez un tableau avec plusieurs lignes représentant les données et les prix correspondants, organisés par commande et par article. Le but est de transformer ces données en un tableau où chaque commande est représentée sous forme de colonne et chaque article est représenté sous forme de ligne, avec les prix correspondants affichés.

Solution :

Pour réaliser cette conversion multi-colonnes, nous utilisons un processus en deux étapes.

Étape 1 : Annuler le pivotement du Données

Nous commençons par annuler le pivotement des données en utilisant UNION ALL pour créer plusieurs lignes pour chaque combinaison de commande et d'article. Cela nous permet de manipuler les données plus facilement à l'étape suivante.

SELECT id, CONCAT('order', `order`) col, data value
FROM tableA
UNION ALL
SELECT id, CONCAT('item', item) col, price value
FROM tableA;

Étape 2 : Faire pivoter les données non pivotées

Une fois les données non pivotées, nous pivotons retournez-le dans les colonnes à l'aide de fonctions d'agrégation avec des instructions CASE. Cela regroupe les lignes non pivotées par ID et attribue les valeurs correspondantes aux colonnes appropriées.

SELECT
  id, 
  MAX(CASE WHEN col = 'order1' THEN value END) ORDER1,
  MAX(CASE WHEN col = 'order2' THEN value END) ORDER2,
  MAX(CASE WHEN col = 'order3' THEN value END) ORDER3,
  MAX(CASE WHEN col = 'item1' THEN value END) ITEM1,
  MAX(CASE WHEN col = 'item2' THEN value END) ITEM2,
  MAX(CASE WHEN col = 'item3' THEN value END) ITEM3
FROM
(
  SELECT id, CONCAT('order', `order`) col, data value
  FROM tableA
  UNION ALL
  SELECT id, CONCAT('item', item) col, price value
  FROM tableA
) d
GROUP BY id;

Génération de requêtes dynamiques :

Pour gérer des tables avec un nombre variable de commandes et les éléments, nous pouvons générer la requête dynamiquement à l'aide d'un fichier préparé déclaration.

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(CASE WHEN col = ''',
      col,
      ''' THEN value END) AS `', 
      col, '`')
  ) INTO @sql
FROM
(
  SELECT CONCAT('order', `order`) col
  FROM tableA
  UNION ALL
  SELECT CONCAT('item', `item`) col
  FROM tableA
)d;

SET @sql = CONCAT('SELECT id, ', @sql, ' 
                  FROM
                  (
                    SELECT id, CONCAT(''order'', `order`) col,  data value
                    FROM tableA
                    UNION ALL
                    SELECT id, CONCAT(''item'', item) col, price value
                    FROM tableA
                  ) d
                  GROUP BY id');

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

Résultat :

Le tableau résultant fournit une représentation concise des données, avec chaque commande sous forme de colonne et chaque élément sous forme de ligne, affichant les tarifs correspondants.

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!