sql中行列互换问题

感觉还是蛮全的

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' )  

---------------------------------  

--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    

------------------  

------------------  

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 。  

--------------------  

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