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