here is the problem I am facing. I essentially need to group and then take the values of any unique user, and combine them and delimit them out by " | " into the result for each product.

enter image description here

Any idea how this can be done with Salesforce Marketing Cloud's version of 2005 TSQL?


You could use a CROSS APPLY, STUFF and FOR XML. I believe these all work in SFMC.

select * FROM [UserModels]
SELECT distinct
, STUFF(x.models,1,1,'') models
FROM [UserModels] AS um
    SELECT '|' + rtrim(um0.model)
    FROM [UserModels] AS um0
    WHERE um0.userid = um.userid
    FOR XML PATH('') 
) x ( models )


UserID  Model
5       wood      
5       metal     
6       sand      
7       plastic   
userId  models
5       wood|metal
6       sand
7       plastic
  • Adam, you sir, are a jenius! Thanks so much. I've learned a great deal in this past year from your posts & from the AMPscript Guide book. Thanks again for your awesome help!
    – CIouz
    Apr 6 '20 at 23:54

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.