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

网上的都是一些静态的，用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)

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