当前位置：首页 > news >正文

MySQL动态行转列

news 来源：原创 2024/5/17 12:26:35

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