PostgreSQL Query Help (Crosstab)

Question

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!

Answer 1

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.