简单的sql转置

--创建表
create table score
(
[id] varchar(10),
[name] varchar(10),
score varchar(20),
sc decimal(18,4)
)

go

 

--修改列属性
alter table score
alter column sc decimal(18,2)

go

 

--测试数据

insert into score values('01','a','语文',80)
insert into score values('01','a','数学',70)
insert into score values('01','a','英语',90)
insert into score values('02','b','语文',85)
insert into score values('02','b','数学',75)
insert into score values('02','b','英语',95)

go

 

方法一：

select * from score

select distinct [id],[name],语文=(select sc from score where [id]=s.[id] and score='语文')
     ,数学=(select sc from score where [id]=s.[id] and score='数学')
     ,英语=(select sc from score where [id]=s.[id] and score='英语')
 from score as s

 

方法二：

select [id],[name],语文=max(case score
       when '语文' then sc
   end)
     ,数学=max(case score
       when '数学' then sc
   end)
     ,英语=max(case score
       when '英语' then sc
   end)
from score
group by [id],[name]