/*
行转列例子
*/
if object_id('tempdb..#score','u') is not null
drop table #score
go
create table #score(sname varchar(50),cname varchar(50),score decimal(10,1))
go
insert into #score(sname,cname,score)
select '小明','语文',90
union all
select '小明','数学',80
union all
select '小明','英语',70
union all
select '小李','语文',92.5
union all
select '小李','数学',83.5
go
select sname,
sum((case when cname='语文' then score else 0 end)) as 语文,
sum((case when cname='数学' then score else 0 end)) as 数学,
sum((case when cname='英语' then score else 0 end)) as 英语
from #score
group by sname
go
行转列例子
*/
if object_id('tempdb..#score','u') is not null
drop table #score
go
create table #score(sname varchar(50),cname varchar(50),score decimal(10,1))
go
insert into #score(sname,cname,score)
select '小明','语文',90
union all
select '小明','数学',80
union all
select '小明','英语',70
union all
select '小李','语文',92.5
union all
select '小李','数学',83.5
go
select sname,
sum((case when cname='语文' then score else 0 end)) as 语文,
sum((case when cname='数学' then score else 0 end)) as 数学,
sum((case when cname='英语' then score else 0 end)) as 英语
from #score
group by sname
go