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 |
Advertisement
Answer
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.