Comment convertir dynamiquement des lignes en colonnes dans MySQL en fonction de plusieurs colonnes ?

Conversion dynamique de lignes en colonnes basées sur plusieurs colonnes dans MySQL

Dans une question précédente, une requête MySQL a été utilisée pour convertir dynamiquement des lignes aux colonnes pour une seule colonne. Désormais, nous visons à obtenir le même résultat pour deux colonnes : "données" et "prix".

Problème :

Convertir les données du tableau suivant en colonnes nommé "order1", "order2", "order3", "item1", "item2", "item3" et "item4" en fonction de la "order" et Colonnes "élément".

id	order	data	item	Price
1	1	P	1	50
1	1	P	2	60
1	1	P	3	70
1	2	Q	1	50
1	2	Q	2	60
1	2	Q	3	70
2	1	P	1	50
2	1	P	2	60
2	1	P	4	80
2	3	S	1	50
2	3	S	2	60
2	3	S	4	80

Résultat souhaité :

id	order1	order2	order3	item1	item2	item3	item4
1	P	Q		50	60	70
2	P		S	50	60		80

Solution :

Bien qu'il soit difficile- une requête codée pourrait être utilisée si le nombre de valeurs « commande » et « article » est connu, une solution plus dynamique est requise dans le cadre général cas.

Annuler le pivotement des données :

MySQL ne dispose pas d'une fonction non-pivot, mais un UNION ALL peut être utilisé pour convertir les multiples paires de données en lignes :

select id, concat('order', `order`) col,  data value
from tableA
union all
select id, concat('item', item) col, price value
from tableA;

Conversion des valeurs en colonnes :

Le les données non pivotées peuvent ensuite être reconverties en colonnes à l'aide d'une fonction d'agrégation avec CASE :

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;

Instruction préparée dynamique :

Enfin, la requête peut être convertie en une instruction préparée dynamique utilisant la concaténation basée sur un ensemble et EXECUTE :

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;

Cette approche offre un moyen flexible et dynamique de convertir lignes en colonnes en fonction de plusieurs critères dans MySQL.

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!