I have the following tables:
CREATE TABLE sensor (
id int PRIMARY KEY,
abbrv varchar(255) NOT NULL UNIQUE
);
and
CREATE TABLE readings (
time timestamp without time zone NOT NULL,
device_id int NOT NULL,
sensor_id int REFERENCES sensor (id) NOT NULL,
value float4 NOT NULL
);
How can I query the database so that it returns the table
time device_id abbrv $1 ... abbrv $n
where n ranges over the rows of the table sensor and ‘abbrv $i’ is replaced by the corresponding value in the table sensor?
The following query
SELECT * FROM crosstab(
'SELECT time, device_id, sensor_id, valeur FROM readings ORDER BY 1, 2',
'SELECT id FROM sensor'
) AS (time timestamp without time zone, device_id int, "sensor_1" float4, "sensor_2" float4, "sensor_3" float4);
works up to a certain extent: I need to know how many rows there are in the sensor table and I have the manually set the columns’ name.