MySQL select distinct rows into a comma delimited list column?

MySQLMySQLi Database

You can achieve it with the help of GROUP_CONCAT() function. The syntax is as follows −

SELECT yourColumnName1,yourColumnName2,yourColumnName3,..N,
GROUP_CONCAT(yourColumnName4) as anyAliasName
FROM yourTableName
group by yourColumnName3, yourColumnName1,yourColumnName2;

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table CommaDelimitedList
   -> (
   -> Id int NOT NULL AUTO_INCREMENT,
   -> Name varchar(10),
   -> GroupId int,
   -> CompanyName varchar(15),
   -> RefId int,
   -> PRIMARY KEY(Id)
   -> );
Query OK, 0 rows affected (0.68 sec)

Insert some records in the table using INSERT command. The query is as follows −

mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
   -> values('Larry',5,'Google',162);
Query OK, 1 row affected (0.14 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
   -> values('Larry',5,'Google',5);
Query OK, 1 row affected (0.48 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
   -> values('Larry',5,'Google',4);
Query OK, 1 row affected (0.16 sec)
mysql> insert into CommaDelimitedList(Name,GroupId,CompanyName,RefId)
   -> values('Sam',6,'Amazon',3);
Query OK, 1 row affected (0.31 sec)

Display all records from the table using select statement. The query is as follows −

mysql> select *from CommaDelimitedList;

The following is the output −

+----+-------+---------+-------------+-------+
| Id | Name  | GroupId | CompanyName | RefId |
+----+-------+---------+-------------+-------+
|  1 | Larry |       5 | Google      |   162 |
|  2 | Larry |       5 | Google      |     5 |
|  3 | Larry |       5 | Google      |     4 |
|  4 | Sam   |       6 | Amazon      |     3 |
+----+-------+---------+-------------+-------+
4 rows in set (0.00 sec)

Here is the query to perform a delimited column list −

mysql> select Name,GroupId,CompanyName,
   -> group_concat(RefId) as RefList
   -> from CommaDelimitedList
   -> group by CompanyName, Name,GroupId;

The following is the output −

+-------+---------+-------------+---------+
| Name  | GroupId | CompanyName | RefList |
+-------+---------+-------------+---------+
| Sam   |       6 | Amazon      | 3       |
| Larry |       5 | Google      | 162,5,4 |
+-------+---------+-------------+---------+
2 rows in set (0.00 sec)
raja
Published on 26-Feb-2019 17:49:44
Advertisements