Converting STUFF + FOR XML from SQL to Snowflake

[Project Numbers]= STUFF((
    SELECT ',' + Fin2.[ID]
        FROM table Fin2
        WHERE In.Investment_Key = Fin2.PROJECT_KEY
        FOR XML PATH('')
         ), 1, 1, '')

I can’t quite figure out how to convert this to snowflake as I am still trying to learn it, is there a STUFF equivalent?

Thanks

Answer

It is LISTAGG:

SELECT In.Investment_Key, LISTAGG(Fin2.[ID], ',') AS "Project Numbers"
FROM In
LEFT JOIN table Fin2
  ON  In.Investment_Key = Fin2.PROJECT_KEY 
GROUP BY In.Investment_Key;

STUFF + FOR XML is an old workaround used for version of SQL Server that does not support STRING_AGG natively. The Snwoflake equivalent is LISTAGG function that could work either as aggregate or windowed function.

---

Addendum:

The SQL Standard STUFF equivalent is called OVERLAY with signature as follow:

OVERLAY <left paren> <character value expression>
        PLACING <character value expression>
        FROM <start position>
        [ FOR <string length> ] <right paren>