0

I have a DE that collects information about past reservations for travel. One of the fields is named FareDiscountCode.

I currently have in place a query from that table that combines all of the reservations for a particular email address to give me a total ride count.

What I would also like to do with that query is combine all of the FareDiscountCodes ever used by that email address. Is that possible in the SFMC environment?

My current query looks like this:

select Count(Distinct(b.BookingID)) as "TotalRides", b.EmailAddress
from "REZ-RideHistory" as b

group by b.EmailAddress

would that query look like this?:

select Count(Distinct(b.BookingID)) as "TotalRides", CONCAT(b.FareDiscountCode, " ") AS RedeemPromCodes, b.EmailAddress
from "REZ-RideHistory" as b

group by b.EmailAddress
0

You can give this a go assuming your first query works.

select Count(Distinct(b.BookingID)) as "TotalRides",
 STUFF((
    SELECT ', ' + [FareDiscountCode] 
    FROM [REZ-RideHistory] as A 
    WHERE (b.EmailAddress= A.EmailAddress) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS RedeemPromCodes,
  b.EmailAddress
from [REZ-RideHistory] as b

group by b.EmailAddress

Also make sure the field length for RedeemPromCodes is sufficient

| improve this answer | |

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.