sql server 行列互轉

標籤：arc   nbsp   create   har   form   creat   arch   mat   from   

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 行列互轉