sql行列转换

--行转列

if OBJECT_ID(‘tempdb..#Student‘) is not null
begin
drop table #Student
end

create table #Student
(
CName varchar(50),
Subject varchar(50),
Score int
)

insert into #Student (CName,Subject,Score) values (‘张三‘,‘语文‘,100)
insert into #Student (CName,Subject,Score) values (‘张三‘,‘数学‘,90)
insert into #Student (CName,Subject,Score) values (‘张三‘,‘英语‘,94)
insert into #Student (CName,Subject,Score) values (‘李四‘,‘语文‘,75)
insert into #Student (CName,Subject,Score) values (‘李四‘,‘数学‘,88)
insert into #Student (CName,Subject,Score) values (‘李四‘,‘英语‘,92)

select * from #Student pivot (max(Score) for Subject in ([语文],[数学],[英语])) t

结果：

--列转行

if OBJECT_ID(‘tempdb..#Student‘) is not null
begin
drop table #Student
end

create table #Student
(
CName varchar(50),
[语文] int,
[数学] int,
[英语] int
)

insert into #Student (CName,[语文],[数学],[英语]) values (‘张三‘,100,90,94)
insert into #Student (CName,[语文],[数学],[英语]) values (‘李四‘,75,88,92)

select * from #Student unpivot(Score for Subject in ([语文],[数学],[英语])) t

结果：

sql行列转换

标签：