数据库行转列

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)