Mysql – Query to sort data from a MYSQL 8 table

MySQL

I request your kind support with the following case.
I have this score table as follows:

create table score (idscore int primary key auto_increment,
                    ndeporte varchar(45),
                    category varchar(45),
                    gender varchar(45),
                    team1 varchar(45),
                    score1 int,
                    team2 varchar(45),
                    score2 int,
                    team3 varchar(45),
                    score3 varchar(45)

)

MY SCORE TABLE
MY SCORE TABLE

NOW I WANT TO GET THIS RESULT.

DESIRABLE TABLE

I have doubts on how to order my data from my table so that it shows as well as the image.
I request your kind support

I am working on MYSQL 8

Best Answer

Do it directly:

SELECT idscore,
       ndeporte,
       category,
       gender,
       'ROJO' team1,
       CASE WHEN team1 = 'ROJO' THEN score1
            WHEN team2 = 'ROJO' THEN score2
            WHEN team3 = 'ROJO' THEN score3
            END score1,
       'AMARILLO' team2,
       CASE WHEN team1 = 'AMARILLO' THEN score1
            WHEN team2 = 'AMARILLO' THEN score2
            WHEN team3 = 'AMARILLO' THEN score3
            END score2,
       'TURQUESA' team3,
       CASE WHEN team1 = 'TURQUESA' THEN score1
            WHEN team2 = 'TURQUESA' THEN score2
            WHEN team3 = 'TURQUESA' THEN score3
            END score1
FROM score;
Related Question