I have a table(lets call it mytable) with the below structure. The table records close to 1 Million rows everyday.
id date timestamp licenseid storeid deviceid value
1 2015-06-12 17:36:15 lic0001 1 0add -52
2 2015-06-12 17:36:15 lic0002 1 0add -54
3 2015-06-12 17:36:15 lic0003 1 0add -53
4 2015-06-12 17:36:21 lic0001 1 0add -54
5 2015-06-12 17:36:21 lic0002 1 0add -59
6 2015-06-12 17:36:21 lic0003 1 0add -62
7 2015-06-12 17:36:21 lic0004 1 0add -55
8 2015-06-12 17:36:15 lic0001 1 0bdd -53
9 2015-06-12 17:36:15 lic0002 1 0bdd -52
10 2015-06-12 17:36:15 lic0003 1 0bdd -52
11 2015-06-12 17:36:15 lic0004 1 0bdd -50
12 2015-06-12 17:36:33 lic0002 1 0bdd -54
13 2015-06-12 17:36:33 lic0003 1 0bdd -54
14 2015-06-12 17:36:33 lic0004 1 0bdd -55
15 2015-06-12 17:36:33 lic0005 1 0bdd -40
I need to use the same data above and rearrange it in a new table by End of day as below. What sql query should I use to get the below output and store it in new table called 'newtable' considering my table size. The number of licenseids are variable.
id date timestamp deviceid storeid lic001 lic002 lic003 lic004 lic005....
1 2015-06-12 17:36:15 0add 1 -52 -54 -53
2 2015-06-12 17:36:21 0add 1 -54 -59 -62 -55
3 2015-06-12 17:36:15 0bdd 1 -53 -52 -52 -50
4 2015-06-12 17:36:33 0bdd 1 -54 -54 -55 -40
I need to do so to to perform calculations for each deviceid per timestamp per storeid and arrive at a conversion for each value and finally an unique value for each deviceid per store per timestamp based on a known equation and using individual license values.