展开全部
第一步,依据你上边给的语句创62616964757a686964616fe78988e69d8331333332643337建一个视图create view v_dept
as
select bd_deptdoc.deptcode as deptcode,
bd_deptdoc.deptlevel as deptlevel,
bd_deptdoc.deptname as deptname,
bd_psndoc.psnname as psnname,
bd_psncl.psnclassname as psnclassname,
bd_psncl.psnclasscode as psnclasscode
from bd_psndoc
inner join bd_deptdoc
on bd_psndoc.pk_deptdoc = bd_deptdoc.pk_deptdoc
inner join bd_psncl
on bd_psndoc.pk_psncl = bd_psncl.pk_psncl
第二步,动态执行sql,由于你人员类别可能不止就3种,所以要动态执行declare @sql varchar(4000)
set @sql = 'select deptcode,deptname'
select @sql = @sql + ',sum(isnull(case [psnclassname] when '''+[psnclassname]+''' then 1 end,0)) as
['+[psnclassname]+']'
from (select distinct [psnclassname] from v_dept) as a
select @sql = @sql+' from v_dept group by deptcode,deptname'
exec(@sql)