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)

　　动手写写就会了，呵呵。