1

I have a mysql database register table as the following:

id_register |date       | id_team |id_athlete | id_presence
1           |2018-04-19 | 3       |14         |1
2           |2018-04-19 | 3       |15         |2
3           |2018-04-19 | 3       |16         |1
4           |2018-04-20 | 3       |14         |3
5           |2018-04-20 | 3       |15         |2
6           |2018-04-20 | 3       |16         |1
7           |2018-04-19 | 4       |4          |1
8           |2018-04-19 | 4       |5          |2
9           |2018-04-19 | 4       |6          |1
10          |2018-04-20 | 4       |4          |2
11          |2018-04-20 | 4       |5          |1
12          |2018-04-20 | 4       |6          |1

id_team, id_athlete, id_presence are obviously foreign keys which are linked to the respective text values.

I would like to output presence value for each team as follows (for example for team id= 3)

date       | athlete 14 |  athlete 15 | athlete 16
2018-04-19 |1           |2            |1
2018-04-20 |3           |2            |1

Is it possible or should I reconsider my table ?

Elena Politi
  • 161
  • 2
  • 18
  • 1
    If you have a reasonably small number of athletes which you want to appear in separate columns, then the duplicate link has you completely covered. If you need a large or variable number, then you would need dynamic SQL, but reporting a very large number of columns is probably not user friendly anyway, and you may want to consider that. – Tim Biegeleisen Apr 20 '18 at 05:45
  • Thanks for the answer: actually you are right I need a pivot table. I – Elena Politi Apr 20 '18 at 05:50

0 Answers0