Salesforce Marketing Cloud SQL PIVOT

I’m trying to create a pivot in SFMC to combine multiple rows into one column. The query is running successfully, but not populating correctly. My table has 2 columns: email address and product_sku. Every email address appears 3 times in the table, with a different product_sku next to it. I want the output to have unique email address, with the 3 product_skus

Example file:

Email | product_sku
test@test.com | 12314321131412
test@test.com | 45353643645
test@test.com | 0953242425352

Example desired output:

email | product_sku_header_1 | product_sku_header_2 | product_sku_header_3
———————————————————————————
test@test.com | 12314321131412 | 45353643645 | 0953242425352

My query is:

select
email
, sum(case when product_sku ='%' then 1 else 0 end) as product_sku_header_1
, sum(case when product_sku ='%' then 1 else 0 end) as product_sku_header_2
, sum(case when product_sku ='%' then 1 else 0 end) as product_sku_header_3
FROM data_extension
group by 
email

Thanks in advance!

Answer

You can use row_number() for conditional aggregation:

select email,
       max(case when seqnm = 1 then product_sku end) as  product_sku_header_1,
       max(case when seqnm = 2 then product_sku end) as  product_sku_header_2,
       max(case when seqnm = 3 then product_sku end) as  product_sku_header_3
from (select de.*,
             row_number() over (partition by email order by product_sku) as seqnum
      from data_extension de
     ) de
group by email;

Advertisement

Answer