写了一个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

©著作权归作者所有：来自51CTO博客作者david_yeung的原创作品，如需转载，请注明出处，否则将追究法律责任

MySQL数据库存储过程SQL语句与特殊技巧