In situation, when you do the data report by Select or Joining the data from one-to-many relation tables in MySQL database. You may found data duplication each of row, and you want to grouping it and concatenate different values into one rows. In objective to normalize the results as we see an example in the cover image of this article.
In the actual situation
You have designed two table for your transaction like these.
Player
| Id | Name |
| 1 | Eriksen |
| 2 | Young |
| 3 | Lukaku |
| 4 | Alexis |
Skills
| Id | Skill |
| 1 | Dribbling |
| 2 | Defending |
| 3 | Shooting |
| 4 | Passing |
And you have the relation table for keep the record which represent for one-to-many between table “Player” and “Skills” as follow;
PlayerSkills
| Id | PlayerId | SkillsId |
| 1 | 1 | 1 |
| 2 | 1 | 4 |
| 3 | 2 | 2 |
| 4 | 2 | 4 |
| 5 | 3 | 3 |
| 6 | 4 | 1 |
| 7 | 4 | 3 |
| 8 | 4 | 4 |
One day. Your boss ask you as he would like to know each skills of players. So, you just select the data in table by joining like this;
select p.Name, s.Skill
from Player p
left join PlayerSkills ps on p.Id = ps.PlayerId
inner join Skills s on ps.SkillsId = s.SkillThe report are showing below, with row duplication.
| Name | Skill |
| Eriksen | Dribbling |
| Eriksen | Passing |
| Young | Defending |
| Young | Passing |
| Lukaku | Shooting |
| Alexis | Dribbling |
| Alexis | Passing |
| Alexis | Shooting |
You can use a function in MySQL using GROUP_CONCAT to concatenate values of “skills” and separate by comma (,) following as;
select p.Name, GROUP_CONCAT(s.Skill)
from Player p
left join PlayerSkills ps on p.Id = ps.PlayerId
inner join Skills s on ps.SkillsId = s.Skill
group by p.NameThe incredible results are showing as table below, and your boss are so happy ^^
| Name | Skill |
| Eriksen | Dribbling, Passing |
| Young | Defending, Passing |
| Lukaku | Shooting |
| Alexis | Dribbling, Passing, Shooting |
Remark: GROUP_CONCAT will be useful if others column are duplication.