I'm trying to display the results of a game in a table. My raw data looks like the following:
matchID StageID StringID ShooterID FinalScore
118 1 3 10040 18.008
118 1 2 10040 13.639
118 1 1 10040 9.895
118 1 3 10051 15.464
118 1 2 10051 23.842
118 1 1 10051 21.018
118 1 3 10041 13.601
118 1 2 10041 8.151
118 2 1 10041 49.5
118 2 2 10041 59.5
118 2 3 10041 49.5
118 2 1 10040 69.5
118 2 3 10040 39.5
118 2 1 10051 109.5
118 2 2 10051 89.5
118 2 3 10051 99.5
118 2 2 10040 59.5
... many more rows with a variable number of StageID's
The output table should look like the following:
ShooterID Stage1 Stage2 Total
10039 38.26 188.50 226.76
10040 41.54 168.50 210.04
10041 28.33 158.50 186.83
10042 59.98 355.50 415.48
10043 49.23 198.50 247.73
10047 33.16 218.50 251.66
10048 33.21 358.50 391.71
10051 60.32 298.50 358.82
10052 30.74 278.50 309.24
10053 62.49 268.50 330.99
I've tried doing this with group_concat(), but things got really nasty when I tried to add the Total column.
Now I've decided to try and do this with a stored proceedure using temporary tables. My problem is that I can't figure out how to create a temporary table with a variable number of columns. I did find a similar question, but it was using MSSQL and did not work for mysql. (Procedure to create a table with a variable number of columns)
I did find some examples of doing this with a static number of columns, but creating the temporary table dynamically has me stumped. Can someone point me in the right direction?
