mysql中动态行转列

场景：不确定转换完有多少列且转换完以后要存入临时表以供其他查询使用。

原始数据如下：

一张生产卡号对应多种添加剂，有多少种添加剂就有多少行数据

转换后数据如下：

一张生产卡号对应多种添加剂，有多少种添加剂就有多少列

下面是sql示例：

-- 获取所有不同的添加剂类型
  SET @material_ids = NULL;
  SELECT GROUP_CONCAT(DISTINCT material_id ORDER BY material_id SEPARATOR ',') INTO @material_ids FROM mes_pc_cr_ingredient_add_record;
  -- 动态构建列和插入语句
  SET @sql = CONCAT(
    'ALTER TABLE temp_table_additive ',
    'ADD COLUMN (',
    (SELECT GROUP_CONCAT(CONCAT('`', material_id, '` INT') SEPARATOR ', ') 
     FROM (SELECT DISTINCT material_id FROM mes_pc_cr_ingredient_add_record)AS additives),
    ');'
  );
	
  -- 执行动态SQL语句来添加列
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
 
	 SET @sql = CONCAT(
  'INSERT INTO temp_table_additive (card_number ,',
  (SELECT GROUP_CONCAT(CONCAT(' `', material_id, '`')) 
   FROM (SELECT DISTINCT material_id FROM mes_pc_cr_ingredient_add_record) AS material_ids),
  ') ',
  'SELECT card_number ,',
  (SELECT GROUP_CONCAT(CONCAT(' MAX(CASE WHEN material_id = ''', material_id, ''' THEN weight ELSE 0 END)')) 
   FROM (SELECT DISTINCT material_id FROM mes_pc_cr_ingredient_add_record) AS material_ids),
  ' FROM mes_pc_cr_ingredient_add_record ',
  'GROUP BY card_number;'
	);
 
  -- 执行动态SQL语句来插入数据
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
	
	select * from temp_table_additive;
	DROP TEMPORARY TABLE IF EXISTS temp_table_additive;

这样将查询结果存入临时表temp_table_additive ，也方便后续查询进行联查。