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!

Gordon Linoff · Accepted Answer · 2021-01-17 14:24:29Z

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;

answered Jan 17, 2021 at 14:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Georgia Over a year ago

OMG thank you so much, it worked!!! And if my variable_id are not only Force, vibration and time but they are many more and can be different for each piece_id? I mean, is there any way to loop variable_id and not write them one by one?

Gordon Linoff Over a year ago

@Georgia . . . There is no way in a simple SQL statement. To do that, you would need to write dynamic SQL (i.e. construct the SQL statement as a string and then execute it) or perhaps compromise with some JSON structure.

Collectives™ on Stack Overflow

How can I pivot in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related