Anyone knows what is the cause of the error in my Stuff SQL? i run the query many times and keep receiving this error. This query will get all the ProductCode based on every customer and i want to display in into data extension.
Here's my query:
SELECT DISTINCT b2.ContactId, ProductCode = STUFF( ( SELECT ',' + pcode.[ProductCode] FROM Asset_Salesforce as asset LEFT JOIN Product2_Salesforce as pcode ON pcode.Id =asset.Product2Id LEFT JOIN Contact_Salesforce as con ON asset.ContactId = con.Id WHERE asset.ContactId = con.Id AND pcode.Id = asset.Product2Id FOR XML PATH ('') ), 1, 1, '') FROM [Asset_Salesforce] as asset2 LEFT JOIN [Customer Data Layer_v2] as b2 ON b2.AccountId = asset2.AccountId WHERE b2.AccountId != '' GROUP BY b2.ContactId, asset2.Id