sql 普通行列转换 |
|
本文标签：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 |
