SQL Server行转列使用

最近工作需要一个行转列的需求，在网上找了一下有很多，问同事后自己也总结了一下写了个小例子。

create table score
(
	s_id int primary key identity(101,1),
	p_id varchar(20),
	s_subject varchar(20),
	s_score float,
	s_date datetime
)
insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','语文',68,'2010-01-01');
insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','数学',78,'2010-01-01');
insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','英文',88,'2010-01-01');

insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','语文',78,'2010-02-01');
insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','数学',77,'2010-02-01');
insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','英文',80,'2010-02-01');

insert into score(p_id,s_subject,s_score,s_date) values('xiaobo','数学',100,'2010-03-01');

insert into score(p_id,s_subject,s_score,s_date) values('mengya','语文',98,'2010-01-01');
insert into score(p_id,s_subject,s_score,s_date) values('mengya','数学',89,'2010-01-01');
insert into score(p_id,s_subject,s_score,s_date) values('mengya','英文',78,'2010-01-01');

select * from score

 

--合并统计
select p_id as 学生,
	sum(case s_subject when '数学' then s_score else 0 end) as 数学,
	sum(case s_subject when '语文' then s_score else 0 end) as 语文,
	sum(case s_subject when '英文' then s_score else 0 end) as 英语,
	sum(case s_subject when 'JAVA' then s_score else 0 end) as JAVA 
from score group by p_id

 

--列表统计
select s_id as 主键,p_id as 学生,
	sum(case s_subject when '数学' then s_score else 0 end) as 数学,
	sum(case s_subject when '语文' then s_score else 0 end) as 语文,
	sum(case s_subject when '英文' then s_score else 0 end) as 英语,
	sum(case s_subject when 'JAVA' then s_score else 0 end) as JAVA,
	s_date as 日期
from score group by s_id,p_id,s_date order by month(s_date)

 

--条件列表统计
select s_id as 主键,p_id as 学生,
	sum(case s_subject when '数学' then s_score else 0 end) as 数学,
	sum(case s_subject when '语文' then s_score else 0 end) as 语文,
	sum(case s_subject when '英文' then s_score else 0 end) as 英语,
	sum(case s_subject when 'JAVA' then s_score else 0 end) as JAVA,
	s_date as 日期
from score where p_id='xiaobo'  group by s_id,p_id,s_date order by month(s_date)

 

动手写写就会了，呵呵。