MySQL动态枢轴

MySQL动态枢轴

不幸的是，MySQL没有

PIVOT

函数，但是您可以使用聚合函数和

CASE

语句对其进行建模。对于动态版本，您将需要使用准备好的语句：

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 @sqlFROM  parts;SET @sql = CONCAt('SELECt product_id, ', @sql, '        FROM parts         GROUP BY product_id');PREPARE stmt FROM @sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;

请参见带有演示的SQL Fiddle

如果只有几列，则可以使用静态版本：

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_Idfrom partsgroup by product_id