数据库行转列 mysql

mysql行转列的操作  

SQL:/* MySQL Data Transfer Source Host: localhost Source Database: test Target Host: localhost Target Database: test Date: 2012-2-17 14:27:13 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for stu -- ---------------------------- DROP TABLE IF EXISTS `stu`; CREATE TABLE `stu` ( `id` int(11) NOT NULL auto_increment, `name` varchar(20) default NULL, `score` int(8) default NULL, `subject` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=gb2312; -- ---------------------------- -- Records -- ---------------------------- INSERT INTO `stu` VALUES ('1', '张三', '80', '语文'); INSERT INTO `stu` VALUES ('2', '李四', '90', '化学'); INSERT INTO `stu` VALUES ('3', '王五', '89', '政治'); INSERT INTO `stu` VALUES ('4', '赵四', '75', '物理'); INSERT INTO `stu` VALUES ('5', '张三', '89', '化学'); INSERT INTO `stu` VALUES ('6', '李四', '58', '语文'); INSERT INTO `stu` VALUES ('7', '赵四', '98', '语文');

执行SQL之后看到是这样的: 

SELECT * FROM `stu` LIMIT 0,50

现在需要转换一下,将行转换为列

select name as "姓名", sum(if(subject="语文",score,0)) as 语文, sum(if(subject="政治",score,0)) as 政治, sum(if(subject="化学",score,0)) as 化学, sum(if(subject="物理",score,0)) as 物理 from stu group by name

执行结果如下:

行转列存储过程：

MYSQL 存储过程代码：

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 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 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 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 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

转载于:https://my.oschina.net/u/938777/blog/117731