普通行列转换

例子如下：

假设有张学生成绩表(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')

 

以上是不固定方式，以下是固定方式(固定 '语文','数学','英语' )

 

 select 姓名, sum(decode(课程,'语文', 分数,null)) "语文", sum(decode(课程,'数学', 分数,null)) "数学", sum(decode(课程,'英语', 分数,null)) "英语" from tb group by 姓名;

 

在上述结果的基础上加平均分，总分，得到如下结果：

姓名      语文      数学      物理     平均分    总分
-----------------------------------------------------------------
李四       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