if object_id('tempdb..##tmp_fgzcl') is not null drop table ##tmp_fgzcl
--if object_id('tempdb..##tmp_fgzrb00') is not null drop table ##tmp_fgzrb00
SELECT 部门名称,日期,职务,pz, dj
into ##tmp_fgzcl from gz_fbgz where 日期 in (select rq from gz_rqsz where xz=1) group by 部门名称,日期,职务,pz,dj
--select * from ##tmp_zzcl
declare @s varchar(8000),@s1 varchar(8000),@s2 varchar(8000),@s3 varchar(8000)
--处理项目
set @s=''
select @s=@s+',['+日期+']= max(case 日期 when '''''+日期+''''' then 值 end) ' from (select distinct top 100 日期 from ##tmp_fgzcl order by 日期) a
--select @s
--处理字段(指标)
select @s1='',@s2='',@s3=''
select @s1=@s1+',@'+id+' varchar(8000)'
,@s2=@s2+'
set @'+id+'=''select 部门名称,日期,职务,pz,id='+id+
',项目='''''+name+''''',值='+name+' from ##tmp_fgzcl'''
,@s3=@s3+'+'' union all ''+@'+id
from(
select name,id=cast(colid as varchar),colid
from tempdb..syscolumns where object_id('tempdb..##tmp_fgzcl')=id and name not in('日期','职务','pz','部门名称') ) a order by colid
--select @s1 , @s2, @s3
select @s1=substring(@s1,2,8000),@s3=substring(@s3,16,8000)
if object_id('tempdb..##tmp_fgzrb') is not null drop table ##tmp_fgzrb
exec('declare '+@s1+' '+@s2+'
exec(''select 部门名称, 职务,pz,项目'+@s+' into ##tmp_fgzrb from(''+'
+@s3+'+'') a group by 部门名称,职务,pz,项目,id order by 职务,id'')')
if object_id('tempdb..##tmp_fgzrb0') is not null drop table ##tmp_fgzrb0
--select *,CONVERT(numeric(15, 2), 0) 合计 into ##tmp_fgzrb0 from ##tmp_fgzrb
select * into ##tmp_fgzrb0 from ##tmp_fgzrb
set @s0=''
select @s0=@s0+'+case when ['+name+'] is null then 0 else ['+name+'] end ' from(select name
from tempdb..syscolumns where object_id('tempdb..##tmp_fgzrb0')=id and name not in('项目','职务','pz','部门名称'))a
select @s0=substring(@s0,2,8000)
--select @sql='update ##tmp_fgzrb0 set 合计=a.aa from (
--select 部门名称,职务,pz,项目,sum('+@s0+') aa from ##tmp_fgzrb0 group by 部门名称,职务,pz,项目)a
--where ##tmp_fgzrb0.职务=a.职务 and ##tmp_fgzrb0.pz=a.pz and ##tmp_fgzrb0.部门名称=a.部门名称'
exec(@sql)
drop table ##tmp_fgzrb
drop table ##tmp_fgzcl
select * from ##tmp_fgzrb0 order by 职务
--select identity(int) 编号,* into ##tmp_fgzrb00 from ##tmp_fgzrb0 order by 职务
--select * from ##tmp_fgzrb00
GO