sql 普通行列转换

问题：假设有张学生成绩表（tb）如下:
姓名课程分数
张三语文 74
张三数学 83
张三物理 93
李四语文 74
李四数学 84
李四物理 94
想变成（得到如下结果）：
姓名语文数学物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/
create table tb（姓名 varchar（10） , 课程 varchar（10） , 分数 int）
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）
go
--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。（以下同）
select 姓名 as 姓名 ,
max（case 课程 when "语文" then 分数 else 0 end）语文,
max（case 课程 when "数学" then 分数 else 0 end）数学,
max（case 课程 when "物理" then 分数 else 0 end）物理
from tb
group by 姓名
--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。（以下同）
declare @sql varchar（8000）
set @sql = "select 姓名 "
select @sql = @sql + " , max（case 课程 when """ + 课程 + """ then 分数 else 0 end） [" + 课程 + "]"
from （select distinct 课程 from tb） as a
set @sql = @sql + " from tb group by 姓名"
exec（@sql）
--SQL SERVER 2005 静态SQL。
select * from （select * from tb） a pivot （max（分数） for 课程 in （语文,数学,物理）） b
--SQL SERVER 2005 动态SQL。
declare @sql varchar（8000）
select @sql = isnull（@sql + "],[" , ""） + 课程 from tb group by 课程
set @sql = "[" + @sql + "]"
exec （"select * from （select * from tb） a pivot （max（分数） for 课程 in （" + @sql + "）） b"）
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/
--SQL SERVER 2000 静态SQL。
select 姓名姓名,
max（case 课程 when "语文" then 分数 else 0 end）语文,
max（case 课程 when "数学" then 分数 else 0 end）数学,
max（case 课程 when "物理" then 分数 else 0 end）物理,
cast（avg（分数*1.0） as decimal（18,2））平均分,
sum（分数）总分
from tb
group by 姓名
--SQL SERVER 2000 动态SQL。
declare @sql varchar（8000）
set @sql = "select 姓名 "
select @sql = @sql + " , max（case 课程 when """ + 课程 + """ then 分数 else 0 end） [" + 课程 + "]"
from （select distinct 课程 from tb） as a
set @sql = @sql + " , cast（avg（分数*1.0） as decimal（18,2））平均分 , sum（分数）总分 from tb group by 姓名"
exec（@sql）
--SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
（select * from （select * from tb） a pivot （max（分数） for 课程 in （语文,数学,物理）） b） m,
（select 姓名 , cast（avg（分数*1.0） as decimal（18,2））平均分 , sum（分数）总分 from tb group by 姓名） n
where m.姓名 = n.姓名
--SQL SERVER 2005 动态SQL。
declare @sql varchar（8000）
select @sql = isnull（@sql + "," , ""） + 课程 from tb group by 课程
exec （"select m.* , n.平均分 , n.总分 from
（select * from （select * from tb） a pivot （max（分数） for 课程 in （" + @sql + "）） b） m ,
（select 姓名 , cast（avg（分数*1.0） as decimal（18,2））平均分 , sum（分数）总分 from tb group by 姓名） n
where m.姓名 = n.姓名"）
drop table tb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三 74 83 93
李四 74 84 94
想变成（得到如下结果）：
姓名课程分数
---- ---- ----
李四语文 74
李四数学 84
李四物理 94
张三语文 74
张三数学 83
张三物理 93
--------------
*/
create table tb（姓名 varchar（10） , 语文 int , 数学 int , 物理 int）
insert into tb values（"张三",74,83,93）
insert into tb values（"李四",74,84,94）
go
--SQL SERVER 2000 静态SQL。
select * from
（
select 姓名 , 课程 = "语文" , 分数 = 语文 from tb
union all
select 姓名 , 课程 = "数学" , 分数 = 数学 from tb
union all
select 姓名 , 课程 = "物理" , 分数 = 物理 from tb
） t
order by 姓名 , case 课程 when "语文" then 1 when "数学" then 2 when "物理" then 3 end
--SQL SERVER 2000 动态SQL。
--调用系统表动态生态。
declare @sql varchar（8000）
select @sql = isnull（@sql + " union all " , "" ） + " select 姓名 , [课程] = " + quotename（Name , """"） + " , [分数] = " + quotename（Name） + " from tb"
from syscolumns
where name！ = N"姓名" and ID = object_id（"tb"） --表名tb，不包含列名为姓名的其它列
order by colid asc
exec（@sql + " order by 姓名 "）
--SQL SERVER 2005 动态SQL。
select 姓名 , 课程 , 分数 from tb unpivot （分数 for 课程 in（[语文] , [数学] , [物理]）） t
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。
--------------------
/*
问题：在上述的结果上加个平均分，总分，得到如下结果：
姓名课程分数
---- ------ ------
李四语文 74.00
李四数学 84.00
李四物理 94.00
李四平均分 84.00
李四总分 252.00
张三语文 74.00
张三数学 83.00
张三物理 93.00
张三平均分 83.33
张三总分 250.00
------------------
*/
select * from
（
select 姓名 as 姓名 , 课程 = "语文" , 分数 = 语文 from tb
union all
select 姓名 as 姓名 , 课程 = "数学" , 分数 = 数学 from tb
union all
select 姓名 as 姓名 , 课程 = "物理" , 分数 = 物理 from tb
union all
select 姓名 as 姓名 , 课程 = "平均分" , 分数 = cast（（语文 + 数学 + 物理）*1.0/3 as decimal（18,2）） from tb
union all
select 姓名 as 姓名 , 课程 = "总分" , 分数 = 语文 + 数学 + 物理 from tb
） t
order by 姓名 , case 课程 when "语文" then 1 when "数学" then 2 when "物理" then 3 when "平均分" then 4 when "总分" then 5 end
drop table tb