sql server行列轉換案例

一、行轉列

建表：
if object_id('mytest')is not null drop table mytest
go
create table mytest(姓名 varchar(10),課程 varchar(10),分數 int)
insert into mytest values('小明','化學',86)
insert into mytest values('小明','數學',77)
insert into mytest values('小明','物理',87)
insert into mytest values('小華','化學',86)
insert into mytest values('小華','數學',84)
insert into mytest values('小華','物理',94)
go

select * from mytest

姓名         課程         分數
---------- ---------- -----------
小明         化學         86
小明         數學         77
小明         物理         87
小華         化學         86
小華         數學         84
小華         物理         94

(6 行受影響)

現在的問題是：我想根據姓名統計這個人的三門成績，即：姓名   化學   數學  物理
首先看看使用case when end結構的時候：
select 姓名,
 max(case 課程 when '化學' then 分數 else 0 end) 化學,
 max(case 課程 when '數學' then 分數 else 0 end) 數學,
 max(case 課程 when '物理' then 分數 else 0 end) 物理
from mytest
group by 姓名

姓名        化學        數學        物理
---------- ----------- ----------- -----------
小華         86          84          94
小明         86          77          87
(2 行受影響)

這個結果就是我們想要的，然後再看看使用pivot：
select * from mytest pivot(max(分數) for 課程 in (化學,數學,物理))a

姓名         化學        數學       物理
---------- ----------- ----------- -----------
小華         86          84          94
小明         86          77          87
(2 行受影響)

求出平均分、總分：
select 姓名,
max(case 課程 when '化學' then 分數 else 0 end) 化學,
max(case 課程 when '數學' then 分數 else 0 end) 數學,
max(case 課程 when '物理' then 分數 else 0 end) 物理,
sum(分數) 總分,
cast(avg(分數*1.0) as decimal(18,2)) 平均分
from mytest
group by 姓名

姓名       化學        數學        物理        總分      平均分
---------- ----------- ----------- ----------- --------- ---------
小華        86          84          94          264      88.00
小明        86          77          87          250      83.33

二、列轉行
1、建立表格
if object_id('mytest') is not null
drop table mytest
go
create table mytest(姓名 varchar(10),化學 int,數學 int,物理 int)
insert into mytest values('小明',86,77,87)
insert into mytest values('小華',86,84,94)
go
select * from mytest
go
姓名       化學        數學        物理
---------- ----------- ----------- -----------
小明       86          77          87
小華       86          84          94

使用SQL Server 2005靜態SQL

select 姓名,課程,分數 from mytest unpivot (分數 for 課程 in([化學],[數學],[物理])) t