Add Column to Result of Postgres Dynamic Query

You may find the count of all systems for a specific (Date,Scenario) by using Count(*) over (Partition By Scenario,Date), and the count of only passed systems for that (Date,Scenario) by using Aggregate Filter as the following, count(*) filter (where Result='PASS') over (Partition By Scenario,Date).

Then compare the two counts, if they are equal then set the overall result as PASS.

Consider the following:

Create Or Replace Function pvt()
    RETURNS void
    LANGUAGE 'plpgsql'
As $body$
declare
    sqlColumn varchar;
    qr varchar;
    columnlist varchar;
Begin
sqlColumn= 'select distinct D.sys_cat from (select Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D order by D.sys_cat;';
qr='prepare pvtstmt AS Select D.Scenario,D.Date,';

For columnlist In EXECUTE sqlColumn  
   Loop
      qr=qr || 'Max(Case When sys_cat=' || columnlist || ' Then (System || ''_'' || Result) Else '''' End) As System' ||  columnlist ||  ' , ';
   END LOOP;
   
   --qr=substr(qr, 0, length(qr) - 1);
   qr=qr|| 'case when D.tc= D.pc then ''PASS'' else ''FAIL'' end as overall ';
   qr=qr || 'From (select *, Count(*) over (Partition By Scenario,Date) tc,'||
   ' count(*) filter (where Result=''PASS'') over (Partition By Scenario,Date) pc,'||
   '  Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D Where D.Scenario =' 
   || '''Proj1'' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
   
Deallocate All;
EXECUTE qr;
End;
$body$

See a demo from db<>fiddle.

For the second required output, you may use the following function:

Create Or Replace Function pvt(qr_selector int, Scenario_selector varchar default 'all')
    RETURNS void
    LANGUAGE plpgsql AS
$body$
declare
    sqlColumn varchar;
    qr varchar; qr2 varchar;
    columnlist varchar;
Begin
  sqlColumn= 'select distinct D.sys_cat from (select Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D order by D.sys_cat;';
  qr='Select D.Scenario,D.Date,';

  For columnlist In EXECUTE sqlColumn  
   Loop
      qr=qr || 'Max(Case When sys_cat=' || columnlist || ' Then (System || ''_'' || Result) Else '''' End) As System' ||  columnlist ||  ' , ';
   END LOOP;
   
   --qr=substr(qr, 0, length(qr) - 1);
  qr=qr|| 'case when D.tc= D.pc then ''PASS'' else ''FAIL'' end as overall ';
  qr=qr || 'From (select *, Count(*) over (Partition By Scenario,Date) tc,'||
  ' count(*) filter (where Result=''PASS'') over (Partition By Scenario,Date) pc,'||
  '  Row_Number() Over (Partition By Scenario,Date Order By System) as sys_cat From run) D ';
   
  if Scenario_selector='all' then
    qr=qr || ' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
  else
    qr=qr || 'Where D.Scenario =''' || Scenario_selector || ''' Group By D.Scenario,D.Date,overall Order By D.Scenario,D.Date';
  end if;
   
  qr2='with cte as (' || qr || ') Select date, count(*) filter (where overall=''PASS'') as Num_Of_Passes,' ||
  'count(*) filter (where overall=''FAIL'') as Num_Of_Fails ' ||
  'from cte group by date;';
   
  Deallocate All;

  if qr_selector = 1 then
   EXECUTE 'prepare pvtstmt AS ' || qr;
  else
   EXECUTE 'prepare pvtstmt AS ' || qr2;
  end if;
End;
$body$

The first parameter in the function qr_selector lets you to choose what query to execute, 1 for the first output, 2 for the second output. The second parameter Scenario_selector lets you to filter the Scenario values, where the default value is 'all'.

i.e. to select the first output result for all Scenario values use Select pvt(1);, and to select the second output result for Scenario = 'Proj1' use Select pvt(2,'Proj1');

See a demo from db<>fiddle.