Showing data in a table horizontally

pivotsqlite

I have a table that stores sensor data (in SQLite) and looks like this;

TABLE Timestream:
idTimestream PK autoincrementing integer, 
time int not null, 
value float not null, 
idSensor integer not null FK

Some but not all sensors have matching times, but I'll only be considering those that do. What I want to do is rearrange the table into the following format,based on a set of sensors listed in the query, not whole set from the table:

Time Sensor1 Sensor2 etc.

I was thinking of Creating a temporary table and then inserting time and the first column, then doing a join on the time for subsequent queries, and finally selecting the whole lot. This doesn't sound too efficient though and I was wondering if there was a better way?

Best Answer

It looks to me like you are trying to 'pivot' the data, which can be done with multiple case statements:

test data:

create table timestream(
  idTimestream integer primary key autoincrement
, time int not null
, value float not null
, idSensor integer not null);

insert into timestream(time, value, idSensor) values(1,0.1,100);

insert into timestream(time, value, idSensor) values(1,0.2,101);

insert into timestream(time, value, idSensor) values(1,0.3,102);

insert into timestream(time, value, idSensor) values(2,0.4,101);

query:

select time, sum(case idSensor when 100 then value end) as s100,
             sum(case idSensor when 101 then value end) as s101,
             sum(case idSensor when 102 then value end) as s102
from timestream
group by time;

time | s100 | s101 | s102
:--- | :--- | :--- | :---
1    | 0.1  | 0.2  | 0.3 
2    | null | 0.4  | null

dbfiddle here

Related Question