Salesforce营销云SQL支点

Question

我试图在SFMC中创建一个支点，将多个行合并成一个列。查询正在成功运行，但未正确填充。我的表有2列:电子邮件地址和product_sku。每个电子邮件地址在表中出现3次，旁边有一个不同的product_sku。我希望输出有唯一的电子邮件地址，带有3 product_skus

示例文件：

电子邮件\ product_sku

test@test.com | 12314321131412

test@test.com | 45353643645

test@test.com = 0953242425352

期望输出的示例：

电子邮件

我的查询是：

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

提前感谢！

Answer 1

您可以使用row_number()进行条件聚合：

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;