STUFF and SELECT FOR XML PATH - SQL Server to MariaDB Migration

In SQL Server, STUFF with SELECT FOR XML PATH statement is widely used to concatenate strings from multiple rows into a single row value.

Consider a sample table:

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

In SQL Server you can use FOR XML PATH clause of SELECT statement to aggregate values from multiple rows:

SQL Server:

  -- Concatenate values using ',' delimiter; STUFF function is only used to remove the leading (,)
  SELECT STUFF((SELECT ',' + name FROM cities FOR XML PATH ('')), 1, 1, '');
  # Seville,Warsaw,Boston

In MariaDB you can just use GROUP_CONCAT function with SEPARATOR clause:

MariaDB:

  -- Concatenate values using ',' delimiter
  SELECT GROUP_CONCAT(name SEPARATOR ',') FROM cities;
  # Seville,Warsaw,Boston

For more information, see SQL Server to MariaDB Migration.