I wanted to compile below db table into a proper csv format by not knowing how many of the Node_ID will appear as this will impact the format of the csv header. Already done the sql query part, wanted to ask by any chance I can write a flexible code to form the csv file?
Raw data :-
_id Node_ID Modality Timestamp Value
------- ------- ----------- ------------ ------
4930376 2002 Temperature 24/8/15 6:25 30
4930377 2403 Temperature 24/8/15 6:24 30
4930378 2004 Temperature 24/8/15 6:21 30
4930379 2203 Temperature 24/8/15 6:26 29
4930380 2211 Temperature 24/8/15 6:21 29
4930381 2401 Temperature 24/8/15 6:25 33
4930382 2411 Temperature 24/8/15 6:24 30
4930383 2101 Temperature 24/8/15 6:22 31
4930384 2201 Temperature 24/8/15 6:27 30
4930385 2111 Temperature 24/8/15 6:30 36
4930386 2113 Temperature 24/8/15 6:27 35
4934082 3200 Temperature 24/8/15 10:28 33
4934083 2402 Temperature 24/8/15 10:30 32
4934084 2213 Temperature 24/8/15 10:30 33
4934085 2103 Temperature 24/8/15 10:34 36
4934086 2423 Temperature 24/8/15 10:32 32
4934087 2421 Temperature 24/8/15 10:34 32
4934088 2413 Temperature 24/8/15 10:37 32
4934089 2102 Temperature 24/8/15 10:31 35
4934090 2202 Temperature 24/8/15 10:35 32
4934091 2404 Temperature 24/8/15 10:33 35
4934092 2121 Temperature 24/8/15 10:39 36
4934093 2002 Temperature 24/8/15 10:35 33
4940370 2123 Temperature 24/8/15 17:44 36
4940371 2422 Temperature 24/8/15 17:40 33
4940372 2001 Temperature 24/8/15 17:36 35
4940373 2301 Temperature 24/8/15 17:39 32
4940374 2402 Temperature 24/8/15 17:36 32
4940375 2103 Temperature 24/8/15 17:40 36
4940376 2423 Temperature 24/8/15 17:38 33
4940377 2421 Temperature 24/8/15 17:40 33
Did this sql:-
SELECT AVG(value), _id, Node_ID, Modality, value, Timestamp
FROM yourTable
GROUP BY DATE(Timestamp), HOUR(Timestamp), Node_ID;
From this previous post SQL hour averaging in 24hours
What I wanted to achieve in a csv format:- (First row is header)
Hour Node_ID1Value Node_ID2Value ... etc(as not knowing how many will there be for the nodes)
6:00 30 33 ... etc
....
....
