MYSQL 数据行列交换（交换数据） 存储过程实现

行列转换的存储过程

初始图：

 

 效果图：

实现过程：

1：建表、建数据

CREATE TABLE `changeprice` (

`id` bigint(20) NOT NULL auto_increment,

`sid` bigint(20) NOT NULL,

`datecreated` timestamp NOT NULL defaultCURRENT_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();即可