1

I am trying to write something similar in query studio on SFMC. But keep getting string_agg is not recognized as a built-in function name. Please help. Also, what could be the alternative. Thanks

SELECT STRING_AGG(Genre, ',') AS Result FROM Genres;

Same result when using group_concat

5

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.

And 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 STUFF() and FOR XML PATH().

SELECT a.id,
emailStr = STUFF(
  (
    SELECT ',' + b.email
    FROM [myDE] b
    WHERE a.id = b.id
    FOR XML PATH('')
  ), 1, 1, '')
FROM [myDE]
GROUP BY ID 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.