一个 mysql 行 转 列 的存储过程

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

DELIMITER$$

DROPPROCEDUREIFEXISTS`test`.`sp_row_column_wrap`$$

CREATEDEFINER=`root`@`localhost`PROCEDURE`sp_row_column_wrap`(IN$schema_namevarchar(64),
IN$table_namevarchar(64))
BEGIN
declarecntint(11);
declare$table_rowsint(11);
declareiint(11);
declarejint(11);
declaresint(11);
declarestrvarchar(255);
--Getthecolumnnumberofthetable
selectcount(1)frominformation_schema.columnswheretable_schema=$schema_nameandtable_name=$table_nameintocnt;
--Gettherownumberofthetable
selecttable_rowsfrominformation_schema.tableswheretable_schema=$schema_nameandtable_name=$table_nameinto$table_rows;
--Checkwhetherthetableexistsornot
droptableifexiststest.temp;
createtableifnotexiststest.temp(`1`varchar(255)notnull);
--loop1start
seti=0;
loop1:loop
ifi=$table_rows-1then
leaveloop1;
endif;
set@stmt1=concat('altertabletest.tempadd`',i+2,'`varchar(255)notnull');
prepares1from@stmt1;
executes1;
deallocateprepares1;
seti=i+1;
endlooploop1;
--loop1end;
sets=0;
--loop2start
loop2:loop
--leaveloop2
ifs=cntthen
leaveloop2;
endif;
set@stmt2=concat('selectcolumn_namefrominformation_schema.columnswheretable_schema="',$schema_name,
'"andtable_name="',$table_name,'"limit',s,',1into@temp;');
prepares2from@stmt2;
executes2;
deallocateprepares2;
setj=0;
setstr='select';
--Loop3start
loop3:loop
ifj=$table_rowsthen
leaveloop3;
endif;
set@stmt3=concat('select',@temp,'from',$schema_name,'.',$table_name,'limit',j,',1into@temp2;');
prepares3from@stmt3;
executes3;
setstr=concat(str,'"',@temp2,'"',',');
deallocateprepares3;
setj=j+1;
endlooploop3;
setstr=left(str,length(str)-1);
--insertnewdataintotable
set@stmt4=concat('insertintotest.temp',str,';');
prepares4from@stmt4;
executes4;
deallocateprepares4;
sets=s+1;
endlooploop2;
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