0

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

2
  • Please answer to all comments from previous copy of your question.
    – Akina
    Commented Sep 27, 2019 at 18:10
  • @akina I already answered you, please your support with this topic
    – kenny
    Commented Sep 27, 2019 at 19:03

1 Answer 1

0

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;
2
  • thank you dear @Akina
    – kenny
    Commented Sep 27, 2019 at 21:31
  • I have a question if I want to get the total of each score as I could.
    – kenny
    Commented Oct 4, 2019 at 16:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.