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 colors (name VARCHAR(30));
 
  -- Insert sample rows
  INSERT INTO colors VALUES ('Green');  
  INSERT INTO colors VALUES ('White');
  INSERT INTO colors VALUES ('Blue');

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 colors FOR XML PATH ('')), 1, 1, '');
  /* Green,White,Blue */
 
  -- Ordering by name now
  SELECT STUFF((SELECT ',' + name FROM colors ORDER BY name FOR XML PATH ('')), 1, 1, '');
  /* Blue,Green,White */

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

MariaDB:

  -- Concatenate values using ',' delimiter
  SELECT GROUP_CONCAT(name SEPARATOR ',') FROM colors;
  /* Green,White,Blue */
 
    -- Ordering by name now
  SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ',') FROM colors;
  /* Blue,Green,White */

For more information, see SQL Server to MariaDB Migration.