Neither of these functions are available inside of SFMC Query Activities.
SQL in SFMC is based on SQL Server 2016, but even that is not a full representation (as shown in the official docs here)
STRING_AGG() was introduced in SQL Server 2017, so is not a capability inside of SFMC.
SQL support for the SQL Query Activity is based on, but doesn’t precisely correspond to, SQL Server 2016 capabilities.
GROUP_CONCAT is not available in T-SQL (SQL Server) and is actually part of MySQL. This means it is also not available inside of SFMC SQL.
Here is an alternative solution for creating a delimited string from my blog that may help using
FOR XML PATH().
emailStr = STUFF(
SELECT ',' + b.email
FROM [myDE] b
WHERE a.id = b.id
FOR XML PATH('')
), 1, 1, '')
GROUP BY ID