MySQL GROUP_CONCAT Function - Features, Examples and Equivalents

GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.

Version: MySQL 5.6

Related functionality for GROUP_CONCAT in MySQL:

GROUP_CONVERT allows you concatenating columns values within a group.

Sample data:

   CREATE TABLE cities
   (
     city VARCHAR(70),
     state CHAR(2)
   );
 
   INSERT INTO cities VALUES ('San Francisco', 'CA');
   INSERT INTO cities VALUES ('San Diego', 'CA');
   INSERT INTO cities VALUES ('Los Angeles', 'CA');
 
   INSERT INTO cities VALUES ('Austin', 'TX');
   INSERT INTO cities VALUES ('Houston', 'TX');

The query groups cities by state and returns a list of cities in each state:

   SELECT state, GROUP_CONCAT(city)
   FROM cities
   GROUP BY state;

The result:

Concatenation within a group in other databases:

Oracle:

SQL Server:

PostgreSQL:

Converting GROUP_CONCAT:

Oracle:

Oracle supports LISTAGG function that can concatenate the values within a group. By default, the delimiter is empty string, so we can to specify comma explicitly:

   SELECT state, LISTAGG(city, ',') WITHIN GROUP (ORDER BY NULL)
   FROM cities
   GROUP BY state;

Result:

SQL Server:

SQL Server does not provide an aggregate function to concatenate values within a group. Using a correlated subquery, FOR XML clause and STUFF function we can achieve MySQL GROUP_CONCAT functionality:

   SELECT state,
      STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
   FROM cities c
   GROUP BY state

Result:

How the SQL Server query works:

1. Correlated subquery gets a list of cities for each state (multiple rows if there are multiple cities in state)
2. FOR XML PATH converts multiple rows into a single row
3. STUFF function is used to remove the first comma

PostgreSQL:

PostgreSQL provides STRING_AGG function to concatenate values within a group. You must specify a delimiter:

   SELECT state, STRING_AGG(city, ',') 
   FROM cities
   GROUP BY state;

Result:

• MySQL Reference
• GROUP BY Aggregate Functions - MySQL 5.6 Reference Manual
• LISTAGG - Oracle 11g R2 SQL Language Reference
• Aggregate Functions - PostgreSQL 9.1 Documentation