MYSQL 数据队列交换 存储过程实现

行列转换的存储过程
初始图：

效果图：

实现过程：

1：建表、建数据

CREATE TABLE `changeprice` (   

   `id` bigint(20) NOT NULL auto_increment, 

   `sid` bigint(20) NOT NULL,

    `datecreated` timestamp NOT NULL default CURRENT_TIMESTAMP,

   `price` varchar(50) NOT NULL,

    PRIMARY KEY  (`id`) 

) ; 

插入数据：

(1,1,'2009-05-08','30'),
(2,1,'2009-05-10','50'),
(3,1,'2009-05-11','12'),
(4,1,'2009-05-12','20'),
(5,1,'2009-05-14','50'),
(6,1,'2009-05-15','30'),
(7,3,'2009-05-11','12'),
(8,3,'2009-05-12','30'),
(9,3,'2009-05-14','50'),
(10,3,'2009-05-15','30'),
(11,2,'2009-05-08','30'),
(12,2,'2009-05-09','50'),
(13,2,'2009-05-11','12'),
(14,2,'2009-05-13','20'),
(15,2,'2009-05-14','50'),
(16,2,'2009-05-15','30');

存储过程实现：

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
begin  
             DECLARE done int default 0;  
             DECLARE strDate DATE;  
             DECLARE str varCHAR(10000) default '';  
        DECLARE cur1 CURSOR FOR select DISTINCT(DATE(datecreated)) from changeprice order by datecreated;  
             DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;  
             set done = 0;
             open cur1;  
             REPEAT  
             FETCH cur1 INTO strDate;  
             if  done <> 1 then  
             set str=CONCAT(str, ',', 'SUM(if(datecreated=''', strDate, ''', price, 0))', '''', strDate,'''');  
             end IF;  
             UNTIL done = 1
             END REPEAT;  
             close cur1;
             set @sqlString=CONCAT(' select sid ', str, ' from changeprice group by sid ');  
             prepare sqlstmt from @sqlString;  
             execute sqlstmt;  
             deallocate prepare sqlstmt;  
             set str='';
    end;

编辑器运行 call test();即可