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.