Need help for an SQL query

[Erakis]

Hello,

I have a table that contains many value for many sensors. Here is an short exemple :

[DATE, SENSOR_ID, VALUE] 
... 
"2006-01-01 00:00:00", 8, 8.76 
"2006-01-01 00:00:00", 10, 8.41 
"2006-01-01 00:00:00", 11, 0.45 
"2006-01-01 00:00:00", 12, 66.63 
"2006-01-01 00:00:00", 13, 5.04 
"2006-01-01 00:00:00", 14, 149.72 
"2006-01-01 00:05:00", 8, 8.76 
"2006-01-01 00:05:00", 9, 0.41 
"2006-01-01 00:05:00", 10, 8.41 
"2006-01-01 00:05:00", 11, 0.47 
"2006-01-01 00:05:00", 13, 5.02 
...

Also I have a table containing all sensors (ID, Name, Description, ...)
From the first table I want to sort data as :

[DATE, SENSOR 1, SENSOR 2, SENSOR 3, SENSOR 4, ... ] 
2006-01-01 00:00:00, 8.76, 8.41, 0.45, 66.63, 5.04, 149.72 
2006-01-01 00:05:00, 8.76, 8.41, 8.47, NULL, 5.02, ...

Is it possible and how ?
Many thanks.

 

[r937]

how many sensors in total are there?

r937.com | rudy.ca

 

[Erakis]

Hello,

Thanks for your anwser

No matter how many sensors. It could be 1 or 100. They are listed in my Sensors table.

 

[r937]

can't do that with mysql

you can with microsoft access, though -- it's called a crosstab query

you can fake it in mysql if you know exactly which sensor numbers you want, but if you have up to 100, i don't think you want to write 100 queries and union them together, which is what you'd have to do

r937.com | rudy.ca

 

[Erakis]

How will you proceed using a crosstable query for ONE sensor ? Can you help me with the query ?

PS : Usually I cannot have more than 32 sensors.
So 1 to 32.

Thanks for you help.

 

[TonyGroves]

There was a problem similar to this answered in

http://www.tek-tips.com/viewthread.cfm?qid=1177006&page=1