I am attempting to create an sql query in salesforce marketing cloud...I have a list of email addresses, the number of products they own, and the associated product codes. I would like to set up the query logic so that if someone has multiple products, and one of those products is product 'A', I would like to exclude those email records. In the screenshot is a sample of the data format - if a contact has two products, that contact has has two records in the dataset broken out by each product code, and so on. I have attempted to run this exclusion using the sql below - the result is it excludes the record with product A, but includes the record with product B. My goal is to have the query exclude both records A & B (all records associated to that excluded email address), not A only. Can marketing cloud fulfill this need using sql query/automation studio, and what sql logic would be needed (i.e. is there a function that would allow the product codes to be combined from two rows into comma separated values in one row)... is there any sort of workaround that would solve for this?
select email_address from 'table' where 1=1 and ((product count > 1 and product code not in ('A'))