MySQL GROUP_CONCAT Function - Features, Examples and Equivalents

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

Syntax GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR 'sep'])
Quick Example SELECT GROUP_CONCAT(city) FROM cities GROUP BY state;
Separator Comma (,) by default, '' eliminates separator
NULL Values Skipped
Max Length 1024 by default, specified by group_concat_max_len system variable

Version: MySQL 5.6

Related Functionality in MySQL

Related functionality for GROUP_CONCAT in MySQL:

CONCAT(sep, s1, s2, ...) Concatenates strings
CONCAT_WS(sep, s1, s2, …) Concatenates strings with a separator
|| Concatenation operator enabled if sql_mode PIPES_AS_CONCAT or ANSI
is set (disabled by default)

MySQL GROUP_CONCAT Function Details

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:

CA San Francisco,San Diego,Los Angeles
TX Austin,Houston

MySQL GROUP_CONCAT in Other Databases

Concatenation within a group in other databases:

Oracle:

LISTAGG(exp) WITHIN GROUP Default delimiter is '' (empty string)
WM_CONCAT Undocumented and unsupported Used prior Oracle 11g

SQL Server:

Correlated subquery with FOR XML clause

PostgreSQL:

STRING_AGG Delimiter must be specified, no default delimiter

MySQL GROUP_CONCAT Conversion to Other Databases

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:

CA Los Angeles,San Diego,San Francisco
TX Austin,Houston

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:

CA San Francisco,San Diego,Los Angeles
TX Austin,Houston

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:

CA San Francisco,San Diego,Los Angeles
TX Austin,Houston

Convert Online

Resources