Add Column to Result of Postgres Dynamic Query

I have a table that looks like this:


| Scenario| Date   | System | Result |
| ------- | ------ | ------ | ------ |
| Proj1   | 07-01  | A      | PASS   |
| Proj1   | 07-01  | B      | PASS   |
| Proj1   | 07-01  | C      | PASS   |
| Proj1   | 07-01  | D      | PASS   |
| Proj1   | 07-02  | A      | FAIL   |
| Proj1   | 07-02  | B      | FAIL   |
| Proj1   | 07-02  | C      | FAIL   |
| Proj1   | 07-02  | D      | FAIL   |
| Proj2   | 07-01  | E      | PASS   |
| Proj2   | 07-01  | F      | FAIL   |
| Proj2   | 07-02  | E      | PASS   |
| Proj2   | 07-02  | F      | PASS   |

I rearranged it to this:

| Scenario| Date | System1 | System2 | System3 | System4| Overall|
| ------- | ---- | ------- | ------- | ------- | -------|--------|
| Proj1   | 07-01| A-PASS  | B-PASS  | C-PASS  | D-PASS |PASS    |
| Proj1   | 07-02| A-FAIL  | B-FAIL  | C-FAIL  | D-FAIL |FAIL    |
| Proj2   | 07-01| E-PASS  | F-FAIL  |         |        |FAIL    |
| Proj2   | 07-02| E-PASS  | F-PASS  |         |        |PASS    |

Right now, the order of the systems is alphabetical but I created a mapping table in which it orders the priority of the systems

|Scenario|System | Priority|
|------- |-------| --------|
|Proj1   |A      | 2       |
|Proj1   |B      | 3       |
|Proj1   |C      | 1       |
|Proj1   |D      | 4       |
|Proj2   |E      | 1       |
|Proj2   |F      | 2       |

Is there a way to adjust the order such that it reflects the priority and is as such:

| Scenario| Date | System1 | System2 | System3 | System4| Overall|
| ------- | ---- | ------- | ------- | ------- | -------|--------|
| Proj1   | 07-01| C-PASS  | A-PASS  | B-PASS  | D-PASS |PASS    |
| Proj1   | 07-02| C-FAIL  | A-FAIL  | B-FAIL  | D-FAIL |FAIL    |
| Proj2   | 07-01| E-PASS  | F-FAIL  |         |        |FAIL    |
| Proj2   | 07-02| E-PASS  | F-PASS  |         |        |PASS    |

Solution

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.