MySQL动态行转列_zhuan

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