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?


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);


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


Try creating a view. This will allow you to pull the data you want, in the order you want without having to do a join with a temp table. One thing to note is i don't believe sqlite will allow you to write to a view, so if you want to do more than just look at the data, you may have to use the view to create a temp table.

  • Can you put an ORDER BY in a view in SQLite? I know you can't in SQL Server. – JNK Dec 12 '12 at 17:02
  • I'm not sure, but if it doesn't, you should be able to use order by when you select the data from the view. – Michael Gardner Dec 12 '12 at 17:09
  • @JNK: Yes you can: sql-fiddle – ypercubeᵀᴹ Dec 12 '12 at 17:15
  • ORDER BY should not be allowed in views - what happens if you join two ordered views? Hence the change in SQL Server to be standardized that a view is just a set definition (like a table) and by definition, all sets are unordered in a RDBMS. – Cade Roux Dec 12 '12 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.