一个 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 );
   --  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  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  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  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  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)

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