STUFF and SELECT FOR XML PATH - SQL Server to Oracle 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 Oracle you can just use LISTAGG function with a delimiter as follows:

Oracle:

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

For more information, see SQL Server to Oracle Migration.