sql中行列互换问题

感觉还是蛮全的

/* 

标题 ：普通行列 转换 (version 2.0)  
问题 ：假 设 有 张 学生成 绩 表 (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 课 程  
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