MSSQL行転列

    

 
if object_id('tb')is not null drop table tb  
go  
create table tb([  ] varchar(4),[  ] varchar(10), [  ] int)  
insert  tb values('2012','   ',86)  
insert  tb values('2012','   ',78)  
insert  tb values('2012','   ',93)  
insert  tb values('2013','   ',75)  
insert  tb values('2013','   ',88)  
insert  tb values('2013','   ',94)  
insert  tb values('2014','   ',90)  
insert  tb values('2014','   ',74)  
insert  tb values('2014','   ',74)  


 SQL2000 ，              CASE WHEN  ，SQL2005      PIVOT  

2000  SQL

 
select [  ],  
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 [  ]  
2000  SQL,       
[sql] view plaincopy CODE              
declare @sql varchar(2000)  
set @sql='select [  ]'  
select @sql=@sql+',max(case [  ] when '''+[  ]+''' then [  ] else 0 end) ['+  +']'   
from(select distinct [  ] from tb) t  
set @sql=@sql+' from tb group by [  ]'  
exec(@sql)  

2005  SQL

 
select *  
from tb  
pivot(  
max([  ])  
for [  ]  
in(   ,   ,   )  
)p  

2005  SQL

declare @sql varchar(2000)  
--取得部門数select@sql=isnull(@sql+',')+quotename([部門])from tbグループby[部門]set@sql='select*from tb pivot(max([考課])for[部門]in('+@sql+'))p'exec(@sql)