Multiple SQL statements in one Activity [closed]

Question

We have to check in our transaction database to see if the product is bought. We get the data like: DB1

key	product_1	Product_2	product_3
111	123456789	789456456	456789132
123	487462538	487454521	215650255
487	474414744	012345698	123054045

And this is how the transaction table looks like: DB2

order	key	Product
00111	111	123456789
00111	111	789456456
00112	123	215650255

And to know if the customer bought the product we want to have a sendable data extension that has the following structure:

key	product_1	bought_1	product_2	bought_2	product_3	bought_3
111	123456789	1	789456456	1	456789132
123	487462538		487454521		215650255	1
487	474414744		012345698		123054045

Now I have the following query:

SELECT 
c.key
, c.PRODUCT_1
, c.PRODUCT_2
, c.PRODUCT_3
, '1' as bought_1
FROM DB1 c
WHERE EXISTS (
  SELECT 
  b.Order 
  FROM DB2 b 
  WHERE c.key = b.key 
  AND c.PRODUCT_1 = b.PRODUCT
)

Is there a better or more effective way to also fill in bought_2 and bought_3 instead of running three activities? Or is there a better query that can help me with this problem? Maybe AMPscript, but prefer this option as shown above, to know if they bought a product - then we skip other e-mails if they didn't buy specific products.

Thanks in advance!

Answer 1

I'm not a fan of denormalizing product data into numbered columns. It makes everything more difficult, especially in SFMC where there are limited options for pivoting the data.

Tall and skinny tables are better than short and fat ones.

~Anonymous DBA

If you're only limited to 3 products, then you could do something like this:

SELECT 
  c.[key]
, c.PRODUCT_1
, c.PRODUCT_2
, c.PRODUCT_3
, case 
    when charindex(convert(varchar(20), c.product_1), p.products) > 0 then 1 
    else 0 
  end bought_1
, case 
    when charindex(convert(varchar(20), c.product_2), p.products) > 0 then 1 
    else 0 
  end bought_2
, case 
    when charindex(convert(varchar(20), c.product_2), p.products) > 0 then 1 
    else 0 
  end bought_3
, p.products
FROM DB1 c
outer apply (
  select
    stuff((select '|' + convert(varchar(20),b.product) from db2 b where b.[key] = c.[key] for xml path('')),1,1,'') products
) p

The OUTER APPLY is like a LEFT JOIN that converts the normalized data into a pipe-delimited column named products.

The CASE statement in the outer query is just checking to see if the product exists in the delimited string.

Here's the SQLFiddle for it.