sql普通行列转换

问题：假设有张学生成绩表(tb)如下:
姓名课程分数
张三语文74
张三数学83
张三物理93
李四语文74
李四数学84
李四物理94
想变成(得到如下结果)：
姓名语文数学物理
----------------
李四748494
张三748393
-------------------
*/
createtabletb(姓名varchar(10),课程varchar(10),分数int)
insertintotbvalues("张三","语文",74)
insertintotbvalues("张三","数学",83)
insertintotbvalues("张三","物理",93)
insertintotbvalues("李四","语文",74)
insertintotbvalues("李四","数学",84)
insertintotbvalues("李四","物理",94)
go
--SQLSERVER2000静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select姓名as姓名,
max(case课程when"语文"then分数else0end)语文,
max(case课程when"数学"then分数else0end)数学,
max(case课程when"物理"then分数else0end)物理
fromtb
groupby姓名
--SQLSERVER2000动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare@sqlvarchar(8000)
set@sql="select姓名"
select@sql=@sql+",max(case课程when"""+课程+"""then分数else0end)["+课程+"]"
from(selectdistinct课程fromtb)asa
set@sql=@sql+"fromtbgroupby姓名"
exec(@sql)
--SQLSERVER2005静态SQL。
select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b
--SQLSERVER2005动态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+"],[","")+课程fromtbgroupby课程
set@sql="["+@sql+"]"
exec("select*from(select*fromtb)apivot(max(分数)for课程in("+@sql+"))b")
---------------------------------
/*
问题：在上述结果的基础上加平均分，总分，得到如下结果：
姓名语文数学物理平均分总分
--------------------------
李四74849484.00252
张三74839383.33250
*/
--SQLSERVER2000静态SQL。
select姓名姓名,
max(case课程when"语文"then分数else0end)语文,
max(case课程when"数学"then分数else0end)数学,
max(case课程when"物理"then分数else0end)物理,
cast(avg(分数*1.0)asdecimal(18,2))平均分,
sum(分数)总分
fromtb
groupby姓名
--SQLSERVER2000动态SQL。
declare@sqlvarchar(8000)
set@sql="select姓名"
select@sql=@sql+",max(case课程when"""+课程+"""then分数else0end)["+课程+"]"
from(selectdistinct课程fromtb)asa
set@sql=@sql+",cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby姓名"
exec(@sql)
--SQLSERVER2005静态SQL。
selectm.*,n.平均分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in(语文,数学,物理))b)m,
(select姓名,cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby姓名)n
wherem.姓名=n.姓名
--SQLSERVER2005动态SQL。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+",","")+课程fromtbgroupby课程
exec("selectm.*,n.平均分,n.总分from
(select*from(select*fromtb)apivot(max(分数)for课程in("+@sql+"))b)m,
(select姓名,cast(avg(分数*1.0)asdecimal(18,2))平均分,sum(分数)总分fromtbgroupby姓名)n
wherem.姓名=n.姓名")
droptabletb
------------------
------------------
/*
问题：如果上述两表互相换一下：即表结构和数据为：
姓名语文数学物理
张三74　　83　　93
李四74　　84　　94
想变成(得到如下结果)：
姓名课程分数
------------
李四语文74
李四数学84
李四物理94
张三语文74
张三数学83
张三物理93
--------------
*/
createtabletb(姓名varchar(10),语文int,数学int,物理int)
insertintotbvalues("张三",74,83,93)
insertintotbvalues("李四",74,84,94)
go
--SQLSERVER2000静态SQL。
select*from
(
select姓名,课程="语文",分数=语文fromtb
unionall
select姓名,课程="数学",分数=数学fromtb
unionall
select姓名,课程="物理",分数=物理fromtb
)t
orderby姓名,case课程when"语文"then1when"数学"then2when"物理"then3end
--SQLSERVER2000动态SQL。
--调用系统表动态生态。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+"unionall","")+"select姓名,[课程]="+quotename(Name,"""")+",[分数]="+quotename(Name)+"fromtb"
fromsyscolumns
wherename!=N"姓名"andID=object_id("tb")--表名tb，不包含列名为姓名的其它列
orderbycolidasc
exec(@sql+"orderby姓名")
--SQLSERVER2005动态SQL。
select姓名,课程,分数fromtbunpivot(分数for课程in([语文],[数学],[物理]))t
--SQLSERVER2005动态SQL，同SQLSERVER2000动态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姓名,课程="语文",分数=语文fromtb
unionall
select姓名as姓名,课程="数学",分数=数学fromtb
unionall
select姓名as姓名,课程="物理",分数=物理fromtb
unionall
select姓名as姓名,课程="平均分",分数=cast((语文+数学+物理)*1.0/3asdecimal(18,2))fromtb
unionall
select姓名as姓名,课程="总分",分数=语文+数学+物理fromtb
)t
orderby姓名,case课程when"语文"then1when"数学"then2when"物理"then3when"平均分"then4when"总分"then5end
droptabletb