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