--各合作機構自年初以來每周報告數量
select b.f0010 jg,datepart(ww,a.creat_tm) zc,count(*) cnt into #t
from RPT_D_BSC a,RPT_R_ORG b
where b.f0040='4' and a.creat_tm>'2007-01-01' and a.org_cl=b.org_cl
group by b.f0010,datepart(ww,a.creat_tm)
order by b.f0010,datepart(ww,a.creat_tm)
declare @sql varchar(8000)
set @sql = 'select jg as ' + '姓名'
select @sql = @sql + ', isnull(sum(case zc when ''' + cast(zc as varchar(2)) + ''' then cnt end),0) [' + cast(zc as varchar(2)) + ']'
from (select distinct zc from #t) as a
order by zc
set @sql = @sql + 'from #t group by jg order by jg'
--print @sql
exec(@sql)