数据库表行列转换

不用写存储过程就能实现，也不用特殊的函数就能搞定行列转换了

create table student
(
	s_name varchar(20),
	s_subject varchar(20),
	s_point int
)

insert into student values('张三','数学',86)
insert into student values('李四','数学',78)
insert into student values('张三','语文',67)
insert into student values('张三','英语',90)
insert into student values('李四','英语',88)
insert into student values('李四','语文',70)
insert into student values('王五','英语',90)
insert into student values('王五','数学',88)
insert into student values('王五','语文',70)
select * from student
--如下图：

[img]http://dl.iteye.com/upload/attachment/155831/0d4be6e8-91d3-3dc8-87b5-2c5b77c3b062.jpg[/img]

select s_name as '姓名',s_point as '数学分数' from student where s_subject='数学' order by s_point desc
--如下图：

[img]http://dl.iteye.com/upload/attachment/155833/e321af02-d78f-36f0-974e-0db31d62eda7.jpg[/img]

select s_name as '姓名',avg(s_point) as '平均成绩' from student group by s_name
--如下图：

[img]http://dl.iteye.com/upload/attachment/155835/94f84e36-2794-3023-b6aa-9c27274a6a9c.jpg[/img]

select n.s_name as '姓名',n.shuxue as '数学',n.yuwen as '语文',n.yingyu as '英语',n.pingjun as '平均成绩',n.allcount as '总分' from
(
	select s_name as s_name,
	(select s_point from student where s.s_name=s_name and s_subject='数学') as shuxue,
	(select s_point from student where s.s_name=s_name and s_subject='语文') as yuwen,
	(select s_point from student where s.s_name=s_name and s_subject='英语') as yingyu,
	avg(s_point) as pingjun,
	sum(s_point) as allcount
	from student s group by s_name 

)n order by allcount desc

[img]http://dl.iteye.com/upload/attachment/155837/e5b7402a-27ee-3a17-98c0-d7be1425ac64.jpg[/img]