MySQL query to concatenate records with similar corresponding ids in a single row separated by a special character

For this, you can use CONCAT_WS() along with GROUP_CONCAT(). Let us first create a

mysql> create table DemoTable2016
   -> (
   -> UserId int,
   -> UserName varchar(20)
   -> );
Query OK, 0 rows affected (0.51 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2016 values(1,'Chris');
Query OK, 1 row affected (0.17 sec)

mysql> insert into DemoTable2016 values(2,'Bob');
Query OK, 1 row affected (0.10 sec)

mysql> insert into DemoTable2016 values(1,'David');
Query OK, 1 row affected (0.14 sec)

mysql> insert into DemoTable2016 values(2,'Carol');
Query OK, 1 row affected (0.12 sec)

mysql> insert into DemoTable2016 values(1,'Sam');
Query OK, 1 row affected (0.14 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2016;

This will produce the following output −

+--------+----------+
| UserId | UserName |
+--------+----------+
| 1      | Chris    |
| 2      | Bob      |
| 1      | David    |
| 2      | Carol    |
| 1      | Sam      |
+--------+----------+
5 rows in set (0.00 sec)

Here is the query to concatenate records in a row separated by a special character −

mysql> select group_concat(concat_ws('-',UserName,UserId)) from DemoTable2016 group by UserId;

This will produce the following output −

+----------------------------------------------+
| group_concat(concat_ws('-',UserName,UserId)) |
+----------------------------------------------+
| Chris-1,David-1,Sam-1                        |
| Bob-2,Carol-2                                |
+----------------------------------------------+
2 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 06-Apr-2020

207 Views

Kickstart Your Career

Get certified by completing the course

Get Started