MySQL动态行转列的实现方法

shark888

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

本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u2/69227/showart_689242.html