将一张表中的数据作为列名的存储过程

问题：在做项目的过程中遇到一个人问题：那就是将A表的deco字段的值，作为B表的列来展示，而且这些值的数据是从C表中取到的

方法：本来是想用，一个视图来列出deco字段里面的值和C表中的数据的，但是发现几张表的关联比较复杂（对应我来说）

使用单纯的select语句，是不能得到想要的效果的，在网上找了相关资料后，发现可以用游标来对查询结果集中的每一条

记录来处理，所以自己写出了如下的存储过程，这个存储过程会创建两张表，这两张表的简单关联就可以得到我想要的数据集。

代码：

/*创建过程*/
DELIMITER //
DROP PROCEDURE IF EXISTS update_report //
CREATE PROCEDURE update_report()
BEGIN
    DECLARE done INT DEFAULT 0;
DECLARE sql_alter VARCHAR(256) default '';
DECLARE sql_str VARCHAR(256) default '';
    DECLARE a VARCHAR(200) DEFAULT '';
DECLARE b VARCHAR(200) DEFAULT '';
DECLARE c VARCHAR(200) DEFAULT '';
DECLARE kpi_value VARCHAR(200) DEFAULT '';
    DECLARE mycursor CURSOR FOR SELECT id, name FROM dc_formula WHERE important=1;
DECLARE projectcursor CURSOR FOR SELECT id from management_project;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;


set @sql_alter='CREATE TABLE project_kpis (
id int(11) NOT NULL PRIMARY key AUTO_INCREMENT,project_id VARCHAR(20) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8';
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;

    OPEN mycursor;

    REPEAT 
        FETCH mycursor INTO a,b;
        IF NOT done THEN
set b=REPLACE(b,'[','1');
set b=REPLACE(b,']','1');
set b=REPLACE(b,' ','_');
set b=REPLACE(b,'/','dv');
set @sql_alter=CONCAT('alter table project_kpis add ',b,' varchar(256)');
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;
        END IF;

    UNTIL done END REPEAT;

set done=0;
    CLOSE mycursor;

OPEN projectcursor;
projectcursor:LOOP
FETCH projectcursor INTO c;
IF done = 1 THEN
LEAVE projectcursor;
END IF;
insert into project_kpis(project_id) values(c);

open mycursor;

mycursor:LOOP
    FETCH mycursor INTO a,b; 
    IF done = 1 THEN
       LEAVE mycursor;
     end IF;
set b=REPLACE(b,'[','1');
set b=REPLACE(b,']','1');
set b=REPLACE(b,' ','_');
set b=REPLACE(b,'/','dv');
set @project_id=c;
set @formula_id=a;
select case when value is NULL then '' else value END val into @kpi_value from v_dc_projectreport WHERE project_id=@project_id and formula_id=@formula_id and language_range='All_exclude_jp';
SET @sql_alter = CONCAT('UPDATE project_kpis set ',b,'="',@kpi_value,'" where project_id=',@project_id);
PREPARE sql_str from @sql_alter;
EXECUTE sql_str;

end LOOP mycursor;
CLOSE mycursor; 
SET done=0;
END LOOP projectcursor;
  CLOSE projectcursor;
END //
DELIMITER ;


DELIMITER //
DROP PROCEDURE if EXISTS kpi_report_pro //
CREATE PROCEDURE kpi_report_pro()
BEGIN
DROP table if EXISTS project_info;
create table project_info as SELECT * from  (SELECT
A.*, B.project_year PROJECT_YEAR
FROM
(
SELECT
id PROJECT_ID,
NAME PROJECT_NAME,
version VERSION
FROM
management_project
) A LEFT  JOIN dc_task B on A.PROJECT_ID=B.project_id GROUP BY A.PROJECT_ID) C;
ALTER TABLE project_info ENGINE=MyISAM;
DROP table if EXISTS project_kpis;
call update_report();
select * from project_info t,project_kpis p where t.PROJECT_ID=p.project_id;
END //
DELIMITER ;


/* 调用存储过程，存储过程调用后产生两张表，project_kpis和project_info 执行完后会输出报表结果*/
CALL kpi_report_pro()