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?
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