行列轉換方法(超過3列)

---drop table tb

create table tb(姓名 nvarchar(10) , 课程 nvarchar(10) , 分数 int, Other int )

insert into tb values(N'张三' , N'语文' , 74, 1)

insert into tb values(N'张三' , N'数学' , 83,2)

insert into tb values(N'张三' , N'物理' , 93,3)

insert into tb values(N'李四' , N'语文' , 74,4)

insert into tb values(N'李四' , N'数学' , 84,5)

insert into tb values(N'李四' , N'物理' , 94,5)

create table tb1(姓名 nvarchar(10) , 课程 nvarchar(10) , 分数 int )

insert into tb1 values(N'张三' , N'语文' , 74)

insert into tb1 values(N'张三' , N'数学' , 83)

insert into tb1 values(N'张三' , N'物理' , 93)

insert into tb1 values(N'李四' , N'语文' , 74)

insert into tb1 values(N'李四' , N'数学' , 84)

insert into tb1 values(N'李四' , N'物理' , 94)

go

select * from tb
select * from tb1

--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

max(case 课程 when N'语文' then 分数 else 0 end) 语文,

max(case 课程 when N'数学' then 分数 else 0 end) 数学,

max(case 课程 when N'物理' then 分数 else 0 end) 物理,

max(case 课程 when N'物理' then Other else 0 end) Other

from tb

group by 姓名

--SQL SERVER 2005 静态SQL。
select 姓名,课程,分数, Other from (select 姓名,课程,分数, Other from tb) a pivot (max(分数) for 课程 in (语文,数学,物理,Other1)) b

select * from (select * from tb1) a pivot (max(分数) for 课程 in (语文,数学,物理)) b

select m.* , n.平均分 , n.总分 from
(select * from (select * from tb1) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m
left join
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb1 group by 姓名) n
on m.姓名 = n.姓名

select m.* , pvt.Other from
(select * from (select * from tb1) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m
left join
(SELECT 姓名,
物理 AS 'Other'   --相當於每列的內容,也就是相當 Other欄位以課程來擺成列時，當等於物理時取的值
FROM tb1
PIVOT (SUM(分数) FOR 课程 in (语文,数学,物理) ) AS pvt ) pvt
on m.姓名 = pvt.姓名

--FOR後面的內容是一個欄位，IN的內容是欄位裡各種CASE