Hello,
I have a uestion regarding my SQL Code.
My goal is it to switch rows to columns.
ID | NAME | POINTS| DATE
-----------------------------------
1 | KARL | 2 | 2020-05-15
2 | EVA | 0 | 2020-05-15
3 | OTTO | 5 | 2020-05-15
4 | KARL | 3 | 2020-05-16
5 | EVA | 1 | 2020-05-16
6 | OTTO | 7 | 2020-05-16
7 | KARL | 5 | 2020-05-17
8 | EVA | 3 | 2020-05-17
9 | OTTO | 10 | 2020-05-17
Goal:
Name|POINTS|2020-05-15|2020-05-16|2020-05-17
----------------------------------------------------------
KARL|2|3|5
EVA|0|1|3
OTTO|5|7|10
My Code
SELECT
myname,
SUBSTRING_INDEX(group_concat(concat(`points`) separator '|'), '|', 1) AS firstday,
SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(concat(`points`) separator '|'), '|', 2), '|', -1) as secondday,
SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(concat(`points`) separator '|'), '|', 3), '|', -1) AS lastday
FROM
(select * from mytable where mydate >= '2020-05-15' order by myname, mydate) tmp
group by myname
That works, I think with this code.
I want to add a new colum to the table like TEAMS
ID | NAME | TEAM |POINTS| DATE
-----------------------------------
1 | KARL | TEAM1 | 2 | 2020-05-15
2 | EVA | TEAM2 | 0 | 2020-05-15
3 | OTTO | TEAM3 | 5 | 2020-05-15
4 | KARL | TEAM1 | 3 | 2020-05-16
5 | EVA | TEAM2 | 1 | 2020-05-16
6 | OTTO | TEAM3 | 7 | 2020-05-16
7 | KARL | TEAM2 | 5 | 2020-05-17
8 | EVA | TEAM2 | 3 | 2020-05-17
9 | OTTO | TEAM3 | 10 | 2020-05-17
So the output should look like this
Name|TEAM||POINTS|2020-05-15|2020-05-16|2020-05-17
----------------------------------------------------------
KARL|TEAM2|2|3|5
EVA|TEAM2|0|1|3
OTTO|TEAM3|5|7|10
With my code this i snot working correctly. The change of the teams leads to the problem that the new team does not appear and the points are not correct selected.
It selects the last 3 values for the points, but puts the current value for the points to that date, when the change was done.
Like this
Name|TEAM||POINTS|2020-05-15|2020-05-16|2020-05-17
----------------------------------------------------------
KARL|TEAM2|5|3|5
I hope I explained it correct.
I try to have a flexible selection so that changes of the team won't disturb the display of the points.
Thank you very much
With kind regards
Tobi
What I have tried:
Checked manuel for stored procedures