一个 mysql 行 转 列 的存储过程

转一个mysql 行 转 列 的存储过程

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 );
-- Getthecolumnnumberofthetable
select count ( 1 ) from information_schema.columns where table_schema = $schema_name and table_name = $table_name into cnt;
-- Gettherownumberofthetable
select table_rows from information_schema.tables where table_schema = $schema_name and table_name = $table_name into $table_rows;
-- Checkwhetherthetableexistsornot
drop table if exists test. temp ;
create table if not exists test. temp (` 1 ` varchar ( 255 ) not null );
-- loop1start
set i = 0 ;
loop1:loop
if i = $table_rows - 1 then
leaveloop1;
end if ;
set @stmt1 = concat( ' altertabletest.tempadd` ' ,i + 2 , ' `varchar(255)notnull ' );
prepare s1 from @stmt1 ;
execute s1;
deallocate prepare s1;
set i = i + 1 ;
end looploop1;
-- loop1end;
set s = 0 ;
-- loop2start
loop2:loop
-- leaveloop2
if s = cnt then
leaveloop2;
end if ;
set @stmt2 = concat( ' selectcolumn_namefrominformation_schema.columnswheretable_schema=" ' ,$schema_name,
' "andtable_name=" ' ,$table_name, ' "limit ' ,s, ' ,1into@temp; ' );
prepare s2 from @stmt2 ;
execute s2;
deallocate prepare s2;
set j = 0 ;
set str = ' select ' ;
-- Loop3start
loop3:loop
if j = $table_rows then
leaveloop3;
end if ;
set @stmt3 = concat( ' select ' , @temp , ' from ' ,$schema_name, ' . ' ,$table_name, ' limit ' ,j, ' ,1into@temp2; ' );
prepare s3 from @stmt3 ;
execute s3;
set str = concat( str , ' " ' , @temp2 , ' " ' , ' , ' );
deallocate prepare s3;
set j = j + 1 ;
end looploop3;
set str = left ( str ,length( str ) - 1 );
-- insertnewdataintotable
set @stmt4 = concat( ' insertintotest.temp ' , str , ' ; ' );
prepare s4 from @stmt4 ;
execute s4;
deallocate prepare s4;
set s = s + 1 ;
end looploop2;
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)

aid	title
1	111
2	222

query result(3 records)

bid	aid	image	time
1	2	1.gif	2007-08-08
2	2	2.gif	2007-08-09
3	2	3.gif	2007-08-08

query result(7 records)

id	cost	des	Autoid
1	10	aaaa	1
1	15	bbbb	2
1	20	cccc	3
2	80	aaaa	4
2	100	bbbb	5
2	60	dddd	6
3	500	dddd	7

query result(2 records)

1	2
1	2
111	222

query result(4 records)

1	2	3
1	2	3
2	2	2
1.gif	2.gif	3.gif
2007-08-08	2007-08-09	2007-08-08

query result(4 records)

1	2	3	4	5	6	7
1	1	1	2	2	2	3
10	15	20	80	100	60	500
aaaa	bbbb	cccc	aaaa	bbbb	dddd	dddd
1	2	3	4	5	6	7