I have a system which monitors the climate in buildings. There are are sensors for temperature and for humidity.
I have a table which contains the latest value of each sensor that exists in the system. Each building can have multiple sensors of each type but not every building will necessarily have all the same sensors.
I want to be able to retrieve all of the sensors value but instead of having a row per sensor, I want it to be a row per building with each sensor value in a separate column.
Here is the table that contains the latest sensor values :
| building_name | sensor_type | sensor_index | value |
|---|---|---|---|
| Building A | Temperature | 1 | 25 |
| Building A | Temperature | 2 | 26 |
| Building A | Humidity | 1 | 45 |
| Building B | Temperature | 1 | 25 |
| Building B | Humidity | 1 | 47 |
| Building B | Humidity | 2 | 45 |
Here is what I expect to receive from the request to the database:
| building_name | Temperature_1 | Temperature_2 | Humidity_1 | Humidity_2 |
|---|---|---|---|---|
| Building A | 25 | 26 | 45 | null |
| Building B | 25 | null | 47 | 45 |
A new row can be added anytime in the original table. This could be a new sensor in an already existing building or a new sensor from a new building.
How can the pivot can be done with Postgresql? I have already thought about creating a separate table to contain the pivoted view, but as the columns would be dynamically created, I would not have control on the schema of the table.
I also know about crosstab from Postgres but I don’t really understand how I could do this as crosstab need you to know the columns that you want in the result table.
Nico is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1