PostgreSQL Query Help (Crosstab)

user_19643382 0
Viewed 66

I have a table that looks like this:

Project 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 want it to end up like this:

Project Date A B C D E F
Proj1 07-01 PASS PASS PASS PASS
Proj1 07-02 FAIL FAIL FAIL FAIL
Proj2 07-01 PASS PASS FAIL
Proj2 07-02 PASS PASS PASS

I was also wondering if it was possible to produce something like this where the order of the various systems are just alphabetical.

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

I have been trying for the last few days with the crosstab function and I am not able to reproduce the results I want. Any help would be much appreciated - thank you so much!

crosstab postgresql sql
1 Answers

Try the following without using Crosstab:

Select Project,Date_,
Max(Case When System_='A' Then Result_ Else '' End) As A,
Max(Case When System_='B' Then Result_ Else '' End) As B,
Max(Case When System_='C' Then Result_ Else '' End) As C,
Max(Case When System_='D' Then Result_ Else '' End) As D,
Max(Case When System_='E' Then Result_ Else '' End) As E,
Max(Case When System_='F' Then Result_ Else '' End) As F
From your_table
Group By Project,Date_
Order By Project,Date_

See a demo from db-fiddle.

If the System column has an undetermined number of values (as you commented), then you have to use Dynamic SQL. I'm not proffenceinal in that, but the following will pay the bill:

First, create a function to prepare the dynamic statement as the following:

Create Or Replace Function pvt()
    RETURNS void
    LANGUAGE 'plpgsql'
As $body$
Declare
    sqlColumn varchar;
    qr varchar;
    columnlist varchar;
Begin
    sqlColumn= 'select distinct system_ from your_table order by system_;';
    qr='prepare pvtstmt as Select Project,Date_,';
    
    For columnlist In EXECUTE sqlColumn  
      Loop
          qr = qr || 'Max(Case When System_='|| chr(39) || columnlist ||
          chr(39) ||' Then Result_ Else ' ||chr(39)||chr(39)||' End) As 
          ' ||  columnlist || ', ';
      End Loop;

    qr = substr(qr, 0, length(qr) - 1);
    qr = qr || 'From your_table Group By Project,Date_ Order By Project,Date_';
   
    Deallocate All;
    EXECUTE qr;
End;
$body$;

Now, call that function to create the prepared statement and execute that statement as the following:

Select pvt();
Execute pvtstmt;

Where pvtstmt is the name of the prepared statement in the function.

See a demo from db-fiddle.

Note: in the demo, the label $body$ is replaced with a single quote, just to run the function on the fiddle.

For the second version of the output format, (System1, System2,...) instead of system name, you may use a view that return Row_Number() Over (Partition By Project,Date_ Order By System_). So the pvt() function will be as 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 Project,Date_ Order By System_) as sys_cat From your_table) D order by D.sys_cat;';
    qr='prepare pvtstmt as Select D.Project,D.Date_,';
    
    For columnlist In EXECUTE sqlColumn  
      Loop
          qr=qr || ' Max(Case When sys_cat='|| chr(39) || columnlist || chr(39) ||' Then System_ ||' ||chr(39)||'-'||chr(39)||'|| Result_ Else ' ||chr(39)||chr(39)||' End) As System' ||  columnlist ||  ' , ';
      End Loop;

    qr = substr(qr, 0, length(qr) - 1);
    qr=qr || 'From (select *, Row_Number() Over (Partition By Project,Date_ Order By System_) as sys_cat From your_table) D Group By D.Project,D.Date_ Order By D.Project,D.Date_;';
   
    Deallocate All;
    EXECUTE qr;
End;
$body$;

Check this demo.

user_127059120
Related