Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
MySQL select distinct rows into a comma delimited list column?
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)
Updated on: 30-Jul-2019
788 Views
Advertisements