How to concatenate columns based on corresponding duplicate id values in MySQL? Display the duplicate values in the same column separated by slash

For this, you can use GROUP_CONCAT().

Let us first create a table −

mysql> create table DemoTable764 (
   ProductId int,
   ProductPrice int
);
Query OK, 0 rows affected (0.56 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable764 values(101,10000);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable764 values(102,1090);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable764 values(103,4000);
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable764 values(102,3450);
Query OK, 1 row affected (0.19 sec)
mysql> insert into DemoTable764 values(101,20000);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable764 values(104,50000);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable764;

This will produce the following output -

+-----------+--------------+
| ProductId | ProductPrice |
+-----------+--------------+
|       101 |        10000 |
|       102 |         1090 |
|       103 |         4000 |
|       102 |         3450 |
|       101 |        20000 |
|       104 |        50000 |
+-----------+--------------+
6 rows in set (0.00 sec)

Following is the query to concatenate columns with corresponding duplicate id values −

mysql> select ProductId,
   group_concat(ProductPrice SEPARATOR '/') AS ProductPrice from DemoTable764
   group by ProductId;

This will produce the following output -

+-----------+---------------+
| ProductId | ProductPrice  |
+-----------+---------------+
|       101 | 10000/20000   |
|       102 | 1090/3450     |
|       103 | 4000          |
|       104 | 50000         |
+-----------+---------------+
4 rows in set (0.00 sec)

AmitDiwan

Updated on: 2019-09-03T08:27:13+05:30

724 Views

Kickstart Your Career

Get certified by completing the course

Get Started