前端之家

  1. 首页
  2. 问答

Salesforce Marketing Cloud SQL PIVOT

• 问答

我正在尝试在SFMC中创建一个枢轴，以将多行合并为一列。查询正在成功运行，但没有正确填充。我的表有2列：电子邮件地址和product_sku。每个电子邮件地址在表格中显示3次，旁边带有一个不同的product_sku。我希望输出具有唯一的电子邮件地址，并带有3个product_skus

示例文件：

  

电子邮件| product_sku
  test@test.com | 12314321131412
  test@test.com | 45353643645
  test@test.com | 0953242425352

示例所需的输出：

  

电子邮件| product_sku_header_1 | product_sku_header_2 | product_sku_header_3   
---------------------------------------------- -----------------------------------
  test@test.com | 12314321131412 | 45353643645 | 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

谢谢！

rpoke 回答：Salesforce Marketing Cloud SQL PIVOT

您可以使用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;
salesforcesalesforce-marketing-cloudsqlsql-server-2005
本文链接：https://www.f2er.com/3141562.html

大家都在问