写了一个MySQL的行转列的存储过程

wocaosinima

网上的都是一些静态的，用CASE WHEN结构实现。所以我写了一个动态的。　　SP 代码：
　　DELIMITER $$
　　DROP PROCEDURE IF EXISTS `test`.`sp_row_column_wrap`$$
　　CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_row_column_wrap`(IN $schema_name varchar(64),
　　IN $table_name varchar(64))
　　BEGIN
　　declare cnt int(11);
　　declare $table_rows int(11);
　　declare i int(11);
　　declare j int(11);
　　declare s int(11);
　　declare str varchar(255);
　　-- Get the column number of the table
　　select count(1) from information_schema.columns where table_schema=$schema_name and table_name=$table_name into cnt;
　　-- Get the row number of the table
　　select table_rows from information_schema.tables where table_schema = $schema_name and table_name=$table_name into $table_rows;
　　-- Check whether the table exists or not
　　drop table if exists test.temp;
　　create table if not exists test.temp (`1` varchar(255) not null);
　　-- loop1 start
　　set i = 0;
　　loop1:loop
　　if i = $table_rows-1 then
　　leave loop1;
　　end if;
　　set @stmt1 = concat('alter table test.temp add `',i+2,'` varchar(255) not null');
　　prepare s1 from @stmt1;
　　execute s1;
　　deallocate prepare s1;
　　set @stmt1 = '';
　　set i = i + 1;
　　end loop loop1;
　　-- loop1 end;
　　set s = 0;
　　-- loop2 start
　　loop2:loop
　　-- leave loop2
　　if s=cnt then
　　leave loop2;
　　end if;
　　set @stmt2 = concat('select column_name from information_schema.columns where table_schema="',$schema_name,
　　'" and table_name="',$table_name,'" limit ',s,',1 into @temp;');
　　prepare s2 from @stmt2;
　　execute s2;
　　deallocate prepare s2;
　　    set @stmt2 = '';
　　    set j=0;
　　set str = ' select ';
　　-- Loop3 start
　　loop3:loop
　　if j = $table_rows then
　　leave loop3;
　　end if;
　　set @stmt3 = concat('select ',@temp,' from ',$schema_name,'.',$table_name,' limit ',j,',1 into @temp2;');
　　prepare s3 from @stmt3;
　　execute s3;
　　set str = concat(str,'"',@temp2,'"',',');
　　deallocate prepare s3;
　　set @stmt3 = '';
　　set j = j+1;
　　end loop loop3;
　　set str = left(str,length(str)-1);
　　-- insert new data into table
　　set @stmt4 = concat('insert into test.temp',str,';');
　　prepare s4 from @stmt4;
　　execute s4;
　　deallocate prepare s4;
　　set @stmt4 = '';
　　set s=s+1;
　　end loop loop2;
　　END$$
　　DELIMITER ;
　　以下是测试结果：
　　======
　　select * from a;
　　select * from b;
　　select * from salary;
　　call sp_row_column_wrap('test','a');
　　select * from test.temp;
　　call sp_row_column_wrap('test','b');
　　select * from test.temp;
　　call sp_row_column_wrap('test','salary');
　　select * from test.temp;
query result(2 records)
aidtitle11112222query result(3 records)
bidaidimagetime121.gif2007-08-08222.gif2007-08-09323.gif2007-08-08query result(7 records)
idcostdesAutoid110aaaa1115bbbb2120cccc3280aaaa42100bbbb5260dddd63500dddd7query result(2 records)
1212111222query result(4 records)
1231232221.gif2.gif3.gif2007-08-082007-08-092007-08-08query result(4 records)
123456711122231015208010060500aaaabbbbccccaaaabbbbdddddddd1234567