SQL结果集转换--附行转列2

/* 
问题：假设有张学生成绩表(score)如下: 
sname subject scores 
王刚 语文 75 
王刚 数学 83 
王刚 英语 93 
李军 语文 74 
李军 数学 84 
李军 英语 94 
想变成(得到如下结果)：  
sname 语文 数学 英语  
---- ---- ---- ---- 
李军 74   84   94 
王刚 75   83   93 
------------------- 
*/ 
create table tb(sname varchar(10) , subject varchar(10) , scores int)  
insert into tb values('王刚' , '语文' , 75)  
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,指subject只有语文、数学、英语这三门subject。(以下同)  
select sname as sname ,  
  max(case subject when '语文' then scores else 0 end) 语文,  
  max(case subject when '数学' then scores else 0 end) 数学,  
  max(case subject when '英语' then scores else 0 end) 英语  
from tb  
group by sname  
--SQL SERVER 2000 动态SQL,指subject不止语文、数学、英语这三门subject。(以下同)  
declare @sql varchar(8000)  
set @sql = 'select sname ' 
select @sql = @sql + ' , max(case subject when ''' + subject + ''' then scores else 0 end) [' + subject + ']' 
from (select distinct subject from tb) as a  
set @sql = @sql + ' from tb group by sname' 
exec(@sql)   
--SQL SERVER 2005 静态SQL。  
select * from tb a pivot (max(scores) for subject in (语文,数学,英语)) b  
--SQL SERVER 2005 动态SQL。  
declare @sql varchar(8000)  
select @sql = isnull(@sql + '],[' , '') + subject from tb group by subject  
set @sql = '[' + @sql + ']' 
exec ('select * from (select * from tb) a pivot (max(scores) for subject in (' + @sql + ')) b')  
---------------------------------  
/* 
问题：在上述结果的基础上加平均分，总分，得到如下结果： 
sname 语文 数学 英语 平均分 总分  
---- ---- ---- ---- ------ ---- 
李军 74   84   94   84.00  252 
王刚 75   83   93   83.33  251 
*/ 
--SQL SERVER 2000 静态SQL。  
select sname sname,  
  max(case subject when '语文' then scores else 0 end) 语文,  
  max(case subject when '数学' then scores else 0 end) 数学,  
  max(case subject when '英语' then scores else 0 end) 英语,  
  cast(avg(scores*1.0) as decimal(18,2)) 平均分,  
  sum(scores) 总分  
from tb  
group by sname  
--SQL SERVER 2000 动态SQL。  
declare @sql varchar(8000)  
set @sql = 'select sname ' 
select @sql = @sql + ' , max(case subject when ''' + subject + ''' then scores else 0 end) [' + subject + ']' 
from (select distinct subject from tb) as a  
set @sql = @sql + ' , cast(avg(scores*1.0) as decimal(18,2)) 平均分 , sum(scores) 总分 from tb group by sname' 
exec(@sql)   
--SQL SERVER 2005 静态SQL。  
select m.* , n.平均分 , n.总分 from  
(select * from (select * from tb) a pivot (max(scores) for subject in (语文,数学,英语)) b) m,  
(select sname , cast(avg(scores*1.0) as decimal(18,2)) 平均分 , sum(scores) 总分 from tb group by sname) n  
where m.sname = n.sname  
--SQL SERVER 2005 动态SQL。  
declare @sql varchar(8000)  
select @sql = isnull(@sql + ',' , '') + subject from tb group by subject  
exec ('select m.* , n.平均分 , n.总分 from  
(select * from (select * from tb) a pivot (max(scores) for subject in (' + @sql + ')) b) m ,   
(select sname , cast(avg(scores*1.0) as decimal(18,2)) 平均分 , sum(scores) 总分 from tb group by sname) n  
where m.sname = n.sname')  
drop table tb      
------------------  
------------------  
/* 
问题：如果上述两表互相换一下：即表结构和数据为： 
sname 语文 数学 英语 
王刚 75　　83　　93 
李军 74　　84　　94 
想变成(得到如下结果)：  
sname subject scores  
---- ---- ---- 
李军 语文 74 
李军 数学 84 
李军 英语 94 
王刚 语文 75 
王刚 数学 83 
王刚 英语 93 
-------------- 
*/ 
create table tb(sname varchar(10) , 语文 int , 数学 int , 英语 int)  
insert into tb values('王刚',75,83,93)  
insert into tb values('李军',74,84,94)  
go  
--SQL SERVER 2000 静态SQL。  
select * from  
(  
 select sname , subject = '语文' , scores = 语文 from tb   
 union all  
 select sname , subject = '数学' , scores = 数学 from tb  
 union all  
 select sname , subject = '英语' , scores = 英语 from tb  
) t  
order by sname , case subject 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 sname , [subject] = ' + quotename(Name , '''') + ' , [scores] = ' + quotename(Name) + ' from tb' 
from syscolumns   
where name! = N'sname' and ID = object_id('tb') --表名tb，不包含列名为sname的其它列  
order by colid asc  
exec(@sql + ' order by sname ')  
--SQL SERVER 2005 静态SQL。  
select sname , subject , scores from tb unpivot (scores for subject in([语文] , [数学] , [英语])) t  
--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。  
--------------------  
/* 
问题：在上述的结果上加个平均分，总分，得到如下结果： 
sname subject   scores 
---- ------ ------ 
李军 语文   74.00 
李军 数学   84.00 
李军 英语   94.00 
李军 平均分 84.00 
李军 总分   252.00 
王刚 语文   75.00 
王刚 数学   83.00 
王刚 英语   93.00 
王刚 平均分 83.33 
王刚 总分   251.00 
------------------ 
*/ 
select * from  
(  
 select sname as sname , subject = '语文' , scores = 语文 from tb   
 union all  
 select sname as sname , subject = '数学' , scores = 数学 from tb  
 union all  
 select sname as sname , subject = '英语' , scores = 英语 from tb  
 union all  
 select sname as sname , subject = '平均分' , scores = cast((语文 + 数学 + 英语)*1.0/3 as decimal(18,2)) from tb  
 union all  
 select sname as sname , subject = '总分' , scores = 语文 + 数学 + 英语 from tb  
) t  
order by sname , case subject when '语文' then 1 when '数学' then 2 when '英语' then 3 when '平均分' then 4 when '总分' then 5 end  
drop table tb 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/coleling/archive/2010/10/29/5973766.aspx