SQL query for joining two columns into one

Question

I have a table of matches and scores, which looks something like this

match_id | player1 | player2 | player1_score | player2_score |
---------|---------|---------|---------------|---------------|
       1 |       1 |       2 |            30 |            50 |
       2 |       3 |       1 |            35 |            10 |
       3 |       1 |       4 |            40 |            20 |
       4 |       2 |       3 |            20 |            25 |
       5 |       4 |       2 |            65 |            15 |
       6 |       3 |       4 |            10 |            20 |

and I want to make a query with the scores in a single column

match_id | player | opponent | score |
---------|--------|----------|-------|
       1 |      1 |        2 |    30 |
       2 |      3 |        1 |    35 |
       3 |      1 |        4 |    40 |
       4 |      2 |        3 |    20 |
       5 |      4 |        2 |    65 |
       6 |      3 |        4 |    10 |
       1 |      2 |        1 |    50 |
       2 |      1 |        3 |    10 |
       3 |      4 |        1 |    20 |
       4 |      3 |        2 |    25 |
       5 |      2 |        4 |    15 |
       6 |      4 |        3 |    20 |

This would be equivalent to concatenating some columns in the table with others:

match_id |  player | opponent |         score |
---------|---------|----------|---------------|
match_id | player1 |  player2 | player1_score |
match_id | player2 |  player1 | player2_score |

but I'm not not sure what the appropriate operation would be. join is the only operation I know that combines tables, but it seems like the wrong tool here.

Either CONCAT or UNION seems reasonable, but which would be preferable here? And what would the query be?

Answer 1

seems you need an union

    select match_id, player1, player2 opponent, player1_score score
    from my_table 
    union all 
    select match_id, player1, player2 , player2_score 
    from my_table