按类别统计收费金额 同时组织机构按父子关系自动缩进

ALTER proc [dbo].[sp_Report_GetProjectOrgChargeLinkMoney_ZH]
(
@orgid varchar(50),
@sqlwhere varchar(1000)
)
as
begin
DECLARE @sql VARCHAR(8000);
set @sql='WITH NODES  AS 
(
select  par.ID,par.parentid,  
4 AS x2level,
convert(varchar(150),(Space(0) + par.OrgName)) as OrgName ,
convert(varchar(150),'',''+ convert(varchar(150),par.ID))+'','' as ''p'',
CAST(+ID AS NVARCHAR(max)) x2id
FROM Base_Organization par where par.ID='+@orgid+'  and par.deletemark=1
UNION ALL 
SELECT child.ID,child.parentid,
rc.x2level+4 x2level,
convert(varchar(150), (Space(rc.x2level) + child.OrgName))  as OrgName,
convert(varchar(150), RC.p +convert(varchar(150),child.id))  +'',''as ''p'',
RC.x2id +'':|:''+ CAST(child.ID AS NVARCHAR(max)) x2id

 FROM Base_Organization child 
INNER JOIN  NODES  AS RC ON child.ParentID = RC.ID  and child.DeleteMark=1   
)' ;
set @sql+='select a.*,sum(b.[合计（万元）]) as ''合计（万元）''';
SELECT @sql=@sql+',sum (b.['+BOD_Name+']) as ['+BOD_Name+']'
FROM  Base_Dictionary where BOD_Type='a90c83d4-ffc1-4d39-bd47-0366a05c6672' order by BOD_Code
set @sql+=' 
from (
SELECT org.ID,org.ParentID, org.OrgName ''收费主体单位'',x2id FROM nodes org 
) a left join
(
SELECT org.ParentID,org.p,SUM(CONVERT(decimal(18,4),isnull( money,0))) as ''合计（万元）''';
select @sql=@sql+',['+BOD_Name+']=SUM(CONVERT(decimal(18,4),CASE ChargeLink WHEN '''+BOD_id+''' THEN isnull( money,0) ELSE 0 END))'
FROM  Base_Dictionary where BOD_Type='a90c83d4-ffc1-4d39-bd47-0366a05c6672' order by BOD_Code;
set @sql +=
' FROM nodes org left outer join
vw_SearchFeeProject t  on t.orgid=org.ID and t.result=1   '+@sqlwhere+'
GROUP BY org.ParentID,org.p
) b  on b.p like ''%,'' + convert(varchar(50),a.ID)+'',%''
group by a.ID,a.ParentID,a.收费主体单位,a.x2id order by a.x2id
';