SQL: Changing structure of sql table with a million rows

89 views Asked by At

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.

0

There are 0 answers