行变列 sql Server
此文章转载别人的,为了学习记录下,请谅解
if object_id('s') is not null
drop table s
if object_id('c') is not null
drop table c
if object_id('t') is not null
drop table t
create table S(sno int,sname varchar(300))
create table c(cno int,cname varchar(300))
create table t(sno int,cno int,score int)
insert into S(sno,sname)
select 1,'张三'
union
select 2,'李四'
union
select 3,'王五'
union
select 4,'甲六'
insert into c(cno,cname)
select 1,'语文'
union
select 2,'数学'
union
select 3,'英语'
union
select 4,'物理'
union
select 5,'化学'
union
select 6,'历史'
union
select 7,'历史'
insert into t(SNO,CNO,score)
select 1,1,80
union
select 1,2,70
union
select 1,3,50
union
select 1,4,60
union
select 1,5,90
union
select 1,6,60
union
select 2,1,41
union
select 2,2,42
union
select 2,3,53
union
select 2,4,64
union
select 3,1,43
union
select 3,2,44
union
select 3,3,55
union
select 3,4,66
/**//* 不使用动态SQL语句*/
select sname 姓名, max(yw) 语文, max(sx) 数学, max(yy) 英语, max(wl) 物理, max(hx) 化学, max(ls) 历史
from
( select sname ,
(case when cname='语文' then score end) yw,
(case when cname='数学' then score end) sx,
(case when cname='英语' then score end) yy,
(case when cname='物理' then score end) wl,
(case when cname='化学' then score end) hx,
(case when cname='历史' then score end) ls
from t left join s on t.sno=s.sno left join c on t.cno=c.cno
) tmp
group by sname