LISTAGG Function - Oracle to MySQL Migration

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

Consider a sample table:

  CREATE TABLE cities (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO cities VALUES ('Malaga');
  INSERT INTO cities VALUES ('Seville');
  INSERT INTO cities VALUES ('Barcelona');

Oracle:

  -- Concatenate values using ';' delimiter
  SELECT LISTAGG(name, ';') WITHIN GROUP (ORDER BY name)
  FROM cities;
  # Barcelona;Malaga;Seville

MySQL:

  -- Concatenate values using ';' delimiter
  SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ';')
  FROM cities;
  # Barcelona;Malaga;Seville

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

Oracle:

  -- Using the default separator ''
  SELECT LISTAGG(name) WITHIN GROUP (ORDER BY name)
  FROM cities;
  # BarcelonaMalagaSeville

MySQL:

  -- The default separator is comma
  SELECT GROUP_CONCAT(name ORDER BY name)
  FROM cities;
  # Barcelona,Malaga,Seville
 
  -- Specifying the empty string as a separator
  SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR '')
  FROM cities;
  # BarcelonaMalagaSeville

For more information, see Oracle to MySQL Migration.