declare
ItemsV nvarchar2(500);
ItemsT nvarchar2(100);
BadItemCount int;
counter number(3):=1;
ItemRn int;
TextItem nvarchar2(100);
ValueItem nvarchar2(100);
nvcCASE nvarchar2(200);
nvcSQL nvarchar2(1000);
begin
select count(*) into BadItemCount from code_items where codeid=(select codeid from code_main where codename='不良行为档次');
while counter<=BadItemCount
loop
select RN,itemtext,itemvalue into ItemRn ,TextItem,ValueItem from (select ROW_NUMBER() OVER(order by orderno asc) as RN , itemtext,itemvalue from code_items where codeid=(select codeid from Code_Main where codename='不良行为档次') order by orderno asc) a where rn=counter;
nvcCASE:='SUM(CASE WHEN B.actiondc= '''||ValueItem||''' THEN 1 ELSE 0 END) as '|| replace(TextItem,' ','')||',';
ItemsV:=ItemsV||nvcCASE;
counter:=counter+1;
end loop;
nvcSQL:='select '||ItemsV||'
A.ItemText,A.ItemValue
from VIEW_CodeMain_CodeItems A
left join View_Xyxx_CorpBadcreditinfo B
on A.ItemValue=B.AuditStatus
where CodeName=''审核状态'' and ItemValue <=4
group by A.ItemValue,A.ItemText
order by ItemValue;';
execute immediate nvcSQL ;
end;

--一下是单独把语句丢出来的执行
select
SUM(CASE WHEN B.actiondc= 'P1' THEN 1 ELSE 0 END) as P1很严重,
SUM(CASE WHEN B.actiondc= 'P2' THEN 1 ELSE 0 END) as P2较严重,
SUM(CASE WHEN B.actiondc= 'P3' THEN 1 ELSE 0 END) as P3严重,
SUM(CASE WHEN B.actiondc= 'P4' THEN 1 ELSE 0 END) as P4中等,
SUM(CASE WHEN B.actiondc= 'P5' THEN 1 ELSE 0 END) as P5一般,
SUM(CASE WHEN B.actiondc= 'P6' THEN 1 ELSE 0 END) as P6轻微,
A.ItemText,A.ItemValue
from VIEW_CodeMain_CodeItems A
left join View_Xyxx_CorpBadcreditinfo B
on A.ItemValue=B.AuditStatus
where CodeName='审核状态' and ItemValue <=4
group by A.ItemValue,A.ItemText
order by ItemValue;