Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Can you post some sample data along with the desired output? –  bluefeet Dec 12 '12 at 17:13
add comment

2 Answers 2

up vote 4 down vote accepted

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

SQLite Schema:

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

Results:

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

SQL Fiddle here

share|improve this answer
    
I didn't realize there were errors with SQL Fiddle when switching between SQLite engines - gotta let me know these things :) I'll take a look into this, but please let me know if you come across other issues like this. –  Jake Feasel Dec 15 '12 at 6:18
1  
I've just now posted a fix so that your query works with Web SQL too. –  Jake Feasel Dec 15 '12 at 6:39
    
That's great Jake, thanks. Have you got an official BugZilla or something? Incidentally the automatic switch to Web SQL didn't work on my iPad, but manually switching was fine :) –  Jack Douglas Dec 15 '12 at 9:10
    
Yes, I use the github issue list to keep track - github.com/jakefeasel/sqlfiddle/issues. Strange that you had trouble with your iPad - works fine on my iPhone (using both chrome and safari). Who knows. –  Jake Feasel Dec 15 '12 at 15:57
    
of course! I forgot I'm using Atomic on my iWotsits. It works fine in Safari –  Jack Douglas Dec 15 '12 at 16:10
show 1 more comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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