首页 > 代码库 > sql server 行列互转

sql server 行列互转

create database Inter_Transformation
go
create table Tb
(
Uname varchar(30),
Course varchar(30),
Fraction int
)
go
insert into tb values(‘张三‘,‘语文‘,74)

insert into tb values(‘张三‘,‘数学‘,83)

insert into tb values(‘张三‘,‘物理‘,93)

insert into tb values(‘李四‘,‘语文‘,74)

insert into tb values(‘李四‘,‘数学‘,84)

insert into tb values(‘李四‘,‘物理‘,94)

insert into tb values(‘王五‘,‘语文‘,null)

insert into tb values(‘王五‘,‘数学‘,84)

insert into tb values(‘王五‘,‘物理‘,94)

go

select * from Tb

go

select Uname,
MAX(case Course when‘语文‘then Fraction else 0 end)语文,
MAX(case Course when‘数学‘then Fraction else 0 end)数学,
MAX(case Course when‘物理‘then Fraction else 0 end)物理
from tb
group by Uname

 

sql server 行列互转