declare @sql varchar(8000)
set @sql = 'select 姓名,'select @sql = @sql + 'sum(case 科目 when '''+科目+'''
then 分数 else 0 end) as '''+科目+''','
from (select distinct 科目 from mytable where 条件....) as aselect @sql = left(@sql,len(@sql)-1) + ' from mytable where 条件.... group by 姓名'exec(@sql)
go
set @sql = 'select 姓名,'select @sql = @sql + 'sum(case 科目 when '''+科目+'''
then 分数 else 0 end) as '''+科目+''','
from (select distinct 科目 from mytable where 条件....) as aselect @sql = left(@sql,len(@sql)-1) + ' from mytable where 条件.... group by 姓名'exec(@sql)
go
F1 F2 F3
----------------
CAS AF 10
CRS FC 50
INS MH 30
CAS NK 40
CRS AF 60
. . .
. . .
. . . Result:
AF FC NK ...TOTAL1 TOTAL2
---------------------------------------------
70 50 40 160 30 TOTAL1左邊的FIELDS :為 H.F2 的不同的且 H.F1<>'INS' 的所有值的和,
TOTAL1 :為這些值的和(SUM(H.F3) WHERE H.F1<>'INS').
TOTAL2 : SUM(H.F3) WHERE H.F1='INS'.
set @s=''
select @s=@s+'['+F2+']=sum(case when F2='''+F2+''' and F1<>''INS'' then F3 else 0 end),'
from H group by F2
exec('
select '+@s+'TOTAL1=sum(case when F1<>''INS'' then F3 else 0 end),TOTAL2=sum(case when F1=''INS'' then F3 else 0 end)
from H')
create table H(F1 varchar(10),F2 varchar(10),F3 int)
insert H select 'CAS','AF',10
union all select 'CRS','FC',50
union all select 'INS','MH',30
union all select 'CAS','NK',40
union all select 'CRS','AF',60
go--查询
declare @s varchar(8000)
set @s=''
select @s=@s+'['+F2+']=sum(case when F2='''+F2+''' and F1<>''INS'' then F3 else 0 end),'
from H group by F2
exec('
select '+@s+'TOTAL1=sum(case when F1<>''INS'' then F3 else 0 end),TOTAL2=sum(case when F1=''INS'' then F3 else 0 end)
from H')
go--删除测试
drop table H/*--测试结果AF FC MH NK TOTAL1 TOTAL2
----------- ----------- ----------- ----------- ----------- -----------
70 50 0 40 160 30--*/
邹大哥的速度讓我吃惊!!!(我想了半天想不出來:)
熱切期待你的MS-SQL Server杂志的出版!!!