mysql 存储过程方式 实现动态行转列

CREATE DEFINER=`Dcadmin`@`%` PROCEDURE `query_action_company`(IN surveyId varchar(100),IN formatId varchar(100),IN startRow INTEGER(16),IN endRow INTEGER(16),IN latestProcessId varchar(100))
    READS SQL DATA
BEGIN

# 表1.1_原始工时机构汇总_业务类型一,按照指定业态的具体活动在每个公司的用时
 
set @sql =null;
set @tmp =null;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'MAX(IF(a.std_comp_name = ''',
      a.std_comp_name,
      ''', cast(a.comp_action_work_hour as decimal(10,2)), 0)) AS ''',
      a.std_comp_name, '\''
    )
  ) INTO  @sql
FROM 
 rpt_tgt_format_comp_action_work_hour a where survey_id = surveyId and format_id = formatId and process_id = latestProcessId;

Set @tmp =  @sql;

SET  @sql = CONCAT('select a.action_name,b.flow3_name,b.flow4_name,  ',  @sql, ' from rpt_tgt_format_comp_action_work_hour  a left join rpt_map_flow_info b on a.flow4_id = b.flow4_id where  a.survey_id = \'',surveyId,'\'',' and format_id = \'',formatId,'\'',' and process_id = \'',latestProcessId,'\'');                      
 SET @sql = CONCAT( @sql, ' Group by a.action_code,a.action_name order by b.flow3_name,b.flow4_name ');
 

# 如果不分页传入-1就可以
IF startRow  <> -1 then
SET @sql = CONCAT(@sql, ' limit ', startRow, ',',endRow);
END IF; 

# 如果数据为空,也需要返回字段
if @tmp <>''  then  
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
else 
set  @sql = CONCAT('select  a.action_name,b.flow3_name,b.flow4_name FROM rpt_tgt_format_comp_action_work_hour a  left join 
rpt_map_flow_info b on a.flow4_id = b.flow4_id where  a.survey_id = \'',surveyId,'\'',' and a.format_id = \'',formatId,'\'','and a.process_id = \'',latestProcessId,'\'');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
end if;

END