Group by one column and display corresponding records from another column with a separator in MySQL

For this, use GROUP_CONCAT() along with GROUP BY. Here, the GROUP_CONCAT() is used to concatenate data from multiple rows into one field.

Let us first create a table −

mysql> create table DemoTable
(
   PlayerId int,
   ListOfPlayerName varchar(30)
);
Query OK, 0 rows affected (0.52 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values(100,'Chris');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(101,'David');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values(100,'Bob');
Query OK, 1 row affected (0.11 sec)
mysql> insert into DemoTable values(100,'Sam');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values(102,'Carol');
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable values(101,'Tom');
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable values(102,'John');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+----------+------------------+
| PlayerId | ListOfPlayerName |
+----------+------------------+
|      100 | Chris            |
|      101 | David            |
|      100 | Bob              |
|      100 | Sam              |
|      102 | Carol            |
|      101 | Tom              |
|      102 | John             |
+----------+------------------+
7 rows in set (0.00 sec)

Following is the query to group by one column and display results from another column with a separator −

mysql> select PlayerId,group_concat(ListOfPlayerName separator '/') as AllPlayerNameWithSameId from DemoTable
   group by PlayerId;

This will produce the following output −

+----------+-------------------------+
| PlayerId | AllPlayerNameWithSameId |
+----------+-------------------------+
|      100 | Chris/Bob/Sam           |
|      101 | David/Tom               |
|      102 | Carol/John              |
+----------+-------------------------+
3 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 09-Oct-2019

987 Views

Kickstart Your Career

Get certified by completing the course

Get Started