How can I pivot in PostgreSQL?

Question

I'm quite new in PostgeSQL. I don't know very well the version I'm using, I access through ip in a browser, if I'm not wrong it's called pgAdmin 4. I have a database in which I have several id of pieces, and several variables, would be something like that:

piece_id | reference | variable_id | variable_value | date_of_creation
-------------------------------------------------------------------------
20001                  Force         334.6            2020-11-11 11:47:03
20001                  Vibration     6.3              2020-11-11 11:47:59
20001                  Time          11/11/2020 16:43 2020-11-11 11:48:05
20002                  Force         220.4            2020-12-11 10:37:49
20002                  Vibration     10.2             2020-12-11 10:38:01
20002                  Time          12/11/2020 18:47 2020-12-11 10:38:05

And I would like to obtain something like this after pivoting:

piece_id | Force  | Vibration | Time 
-------------------------------------------------
20001      334.6     6.3       11/11/2020 16:43   
20002      220.4     10.2      12/11/2020 18:47

And I was trying to use this code, but I think I'm far away from what I need, I get errors on almost every line:

SELECT * FROM backend_piecedataentry
  variable_id,
  (CASE WHEN piece_id='20001' THEN variable_value END) "20001",
  
  [ORDER BY 1]  

And I need to be able to do it in a loop since I have many piece_id, not only the ones I have indicated.

Thank you very much to all of you who can help me!

Answer 1

Use conditional aggregation, which in Postgres implies the FILTER clause:

SELECT piece_id,
       MAX(variable_value) FILTER (WHERE variable_id = 'force') as force,
       MAX(variable_value) FILTER (WHERE variable_id = 'vibration') as vibration,
       MAX(variable_value) FILTER (WHERE variable_id = 'time') as time
FROM backend_piecedataentry bp
GROUP BY piece_id;