LISTAGG Function - Oracle to MySQL Migration

In Oracle you can use the LISTAGG function to concatenate strings from multiple rows into a single row value.

In MySQL you can use the GROUP_CONCAT function. Note that LISTAGG uses '' empty string as the default separator, whereas GROUP_CONCAT uses a comma by default.

Consider a sample table:

  -- Sample table and data
  CREATE TABLE colors (name VARCHAR(30));
 
  INSERT INTO colors VALUES ('Orange');
  INSERT INTO colors VALUES ('Blue');
  INSERT INTO colors VALUES ('Red');

Oracle:

  -- Concatenate values using ';' delimiter
  SELECT LISTAGG(name, ';') WITHIN GROUP (ORDER BY name)
  FROM colors;
  /* Blue;Orange;Red */

MySQL:

  -- Concatenate values using ';' delimiter
  SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ';')
  FROM colors;
   /* Blue;Orange;Red */

Oracle LISTAGG uses '' empty string as the default separator, while MySQL GROUP_CONCAT uses comma:

Oracle:

  -- Using the default separator ''
  SELECT LISTAGG(name) WITHIN GROUP (ORDER BY name)
  FROM colors;
  /* BlueOrangeRed */

MySQL:

  -- The default separator is comma
  SELECT GROUP_CONCAT(name ORDER BY name)
  FROM colors;
  /* Blue,Orange,Red */
 
  -- Specifying the empty string as a separator
  SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR '')
  FROM colors;
  /* BlueOrangeRed */

For more information, see Oracle to MySQL Migration.