Mysql combine two rows into a single column

Question

I am attempting to combine two rows into a single column on mysql this is getting all two players in 1 single query and combine them using the team column, my table is below.

+-------------------+
| ID  player  Team  |
+-------------------+
| 1  John Doe Team1 |
| 2  Anna Doe Team1 |
+-------------------+

What I want to achieve is below.

+-----------------------------+
| ID  player1  player2  Team  |
+-----------------------------+
| 1  John Doe  Anna Doe Team1 |
+-----------------------------+

I am using PHP as my backend and doing a loop and pushing into a single array can do the job but I believe it can be attained using mysql query. any suggestion would be great.

duplicate of https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field?rq=1 — Denis Alimov, Aug 14 '18 at 10:01
why do you want to do this ? this will kill flexibility, in case you have more than 2 players in a team. — Madhur Bhaiya, Aug 14 '18 at 10:03
Define 'Player 1'. Define 'Player 2'. Also, it's much better to do this kind of thing in application code, as you're already doing! — Strawberry, Aug 14 '18 at 10:13
@DenisAlimov `GROUP_CONCAT` is not working on mysql version that I am using looks like i need to upgrade to use the answer link you provided. — Sarotobi, Aug 14 '18 at 10:23

score 2 · Accepted Answer · answered Aug 14 '18 at 10:28

2

Try this query:

select team, 
       min(player) player1, 
       max(player) player2 
from my_table
group by team

Since there are only two players per team, min and max will return the two.

answered Aug 14 '18 at 10:28

Michał Turczyn

32,028
14
47
69

Agreed, this will work just fine if there are just two players per team. – Ildar Akhmetov Aug 14 '18 at 10:34

Mysql combine two rows into a single column

1 Answers1