如何在Snowflake中实现多聚合字段的Pivot行转列操作

针对原生PIVOT仅支持单聚合函数的问题，有两种常用实现方式：

方案1：CASE WHEN条件聚合（兼容性最强） #

直接按product_id分组，对每个周期+指标的组合写条件聚合逻辑，无需依赖PIVOT语法：

SELECT
  product_id,
  -- 销售额指标
  MAX(CASE WHEN Period = 'L13' THEN REPLACE(Sales, '$', '')::NUMBER END) AS SALES_L13,
  MAX(CASE WHEN Period = 'L26' THEN REPLACE(Sales, '$', '')::NUMBER END) AS SALES_L26,
  MAX(CASE WHEN Period = 'L52' THEN REPLACE(Sales, '$', '')::NUMBER END) AS SALES_L52,
  -- 利润指标
  MAX(CASE WHEN Period = 'L13' THEN REPLACE(Profit, '$', '')::NUMBER END) AS PROFIT_L13,
  MAX(CASE WHEN Period = 'L26' THEN REPLACE(Profit, '$', '')::NUMBER END) AS PROFIT_L26,
  MAX(CASE WHEN Period = 'L52' THEN REPLACE(Profit, '$', '')::NUMBER END) AS PROFIT_L52
FROM your_business_table
GROUP BY product_id
ORDER BY product_id;

注：如果你的Sales、Profit字段本身就是数值类型而非带$的字符串，去掉REPLACE和类型转换逻辑直接用字段名即可。聚合函数用MAX/MIN/SUM均可，只要保证单个product_id+Period组合只有一条数据即可。

方案2：先UNPIVOT再PIVOT（写法更简洁，适合指标较多的场景） #

先将Sales、Profit两个指标拆为行数据，拼接指标前缀和Period值后再做一次PIVOT，仅需一次聚合即可完成：

WITH unpivot_data AS (
  -- 先把两个指标拆成行
  SELECT 
    product_id,
    CONCAT(metric, '_', Period) AS metric_period,
    REPLACE(value, '$', '')::NUMBER AS value
  FROM your_business_table
  UNPIVOT (
    value FOR metric IN (Sales, Profit)
  )
)
-- 再做行转列
SELECT *
FROM unpivot_data
PIVOT (
  MAX(value) FOR metric_period IN (
    'SALES_L13' AS SALES_L13,
    'SALES_L26' AS SALES_L26,
    'SALES_L52' AS SALES_L52,
    'PROFIT_L13' AS PROFIT_L13,
    'PROFIT_L26' AS PROFIT_L26,
    'PROFIT_L52' AS PROFIT_L52
  )
)
ORDER BY product_id;

两种方案执行后输出的结果一致，样例输出如下：

内容的提问来源于stack exchange，提问作者Vipendra Singh