Dynamic transponation of a table

Question

0.00/5 (No votes)

See more:

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

Posted 3-Jun-20 21:29pm

Member 14852988

Updated 5-Jun-20 7:53am

Add a Solution

Comments

Garth J Lancaster 4-Jun-20 4:41am

I think you need to improve your question a little - for instance, why is TEAM2 the Team for Karl here "KARL|TEAM2|2|3|5" ? - ok, it appears to be the last team Karl was in by date or the Max by date ..

Right now I can't see a single query that would do as you ask, I can think of getting a distinct list of names & last team name by date, then using a cursor to iterate through the records for a team member to build the points & dates sections

btw - wtf is 'transponation' ? do you mean 'transposition' perhaps ?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

#realJSOP · Accepted Answer · 2020-06-04T01:26:00

Solution 1

What you want to do is perform a "pivot" on your table. If you're doing in it in a MySQL Server view or stored proc, SQL Pivot in all databases: MySQL, MariaDB, SQLite, PostgreSQL, Oracle, …[^]

Posted 4-Jun-20 1:26am

#realJSOP

v2

Comments

Maciej Los 5-Jun-20 14:00pm

5ed!

Member 14852988 · Accepted Answer · 2020-06-05T07:55:00

Hello,

sorry for the wrong posting.
I tried the hole day and I think I found what I am looking for.

SELECT id, name, team, 
                                MAX(CASE WHEN datum = DATE_SUB(CURDATE(), INTERVAL 4 day) THEN points END) 4tage, 
                                MAX(CASE WHEN datum = DATE_SUB(CURDATE(), INTERVAL 3 day) THEN points END) 3tage, 
                                MAX(CASE WHEN datum = DATE_SUB(CURDATE(), INTERVAL 2 day) THEN points END) 2tage, 
                                MAX(CASE WHEN datum = DATE_SUB(CURDATE(), INTERVAL 1 day) THEN ppoints END) 1tage,
                                MAX(CASE WHEN datum = DATE_SUB(CURDATE(), INTERVAL 0 day) THEN points END) 0tage 
                                FROM `vs_highscore` WHERE datum BETWEEN DATE_SUB(NOW() , INTERVAL 5 day) AND NOW() 
                                GROUP BY name, team ORDER BY team DESC

It is not perfect, but for me it is OK.

Perhaps an experts can tell me what to improve.

I found the solution with the link

#realJSOP

posted.
Than you for that.

With kind regards

Tobi