create table #T(batchId char(6), userId int, targetId int, result int, CdateTime datetime)
insert #t select 100001, 25338, 1001, 300, '2007-10-1'
insert #t select 100001, 25338, 1001, 200, '2007-10-2'
insert #t select 100001, 25338, 1002, 100, '2007-10-1'
insert #t select 100001, 25338, 1002, 50, '2007-10-2'
insert #t select 100001, 25174, 1001, 50, '2007-10-1'
insert #t select 100001, 25174, 1001, 150, '2007-10-2'
insert #t select 100001, 25174, 1002, 160, '2007-10-1'
insert #t select 100001, 25174, 1002, 200, '2007-10-2'
insert #t select 100001, 27746, 1001, 300, '2007-10-1'
insert #t select 100001, 27746, 1001, 150, '2007-10-2'
insert #t select 100001, 27746, 1002, 130, '2007-10-1'
insert #t select 100001, 27746, 1002, 120, '2007-10-2'
godeclare @s nvarchar(4000),@s2 nvarchar(4000),@s3 nvarchar(4000),@s4 nvarchar(4000)
select @s='',@s2='',@s3='',@s4=''
select @s=@s+','+quotename(targetId)+'=sum(case when targetId='+rtrim(targetId)+' then result else 0 end)',
@s2=@s2+',avg('+quotename(targetId)+')',
@s3=@s3+',sum('+quotename(targetId)+')',
@s4=@s4+','+quotename(targetId)
from #T group by targetId
select @s=' select [userId]=rtrim(userId)'+@s+',row=0 from #t group by userId',
@s2=' union all select [userId]=''AVG'''+@s2+',row=1 from ('+@s+')T',
@s3=' union all select [userId]=''SUM'''+@s3+',row=2 from ('+@s+')T '
exec('select userId'+@s4+' from ('+@s+@s2+@s3+') Tmp order by row asc')userId 1001 1002
------------ ----------- -----------
25174 200 360
25338 500 150
27746 450 250
AVG 383 253
SUM 1150 760
select userId,[1001],[1002]
from
( select [userId]=rtrim(userId),[1001]=sum(case when targetId=1001 then result else 0 end),[1002]=sum(case when targetId=1002 then result else 0 end),row=0
from #t group by userId
union all select [userId]='AVG',avg([1001]),avg([1002]),row=1 from ( select [userId]=rtrim(userId),[1001]=sum(case when targetId=1001 then result else 0 end), [1002]=sum(case when targetId=1002 then result else 0 end),row=0 from #t group by userId)T
union all select [userId]='SUM',sum([1001]),sum([1002]),row=2 from ( select [userId]=rtrim(userId),[1001]=sum(case when targetId=1001 then result else 0 end),[1002]=sum(case when targetId=1002 then result else 0 end),row=0 from #t group by userId)T )
Tmp order by row asc