如何在Vertica中对结果集进行动态行转列？

需求说明 #

需将ProductCategorySales表数据按Product分组，把Month、Year与Category1Sales/Category2Sales拼接为新列名（格式如2_2019_Category1Sales），由于产品对应年月不固定，必须通过动态SQL实现行转列。

原始表结构与数据 #

表名：ProductCategorySales

Product, Month, Year, Category1Sales, Category2Sales
P001,    2,     2019, 150,            231
P001,    4,     2022, 120,            52
P002,    1,     2023, 97,             328
P003,    7,     2018, 46,             157

目标输出格式 #

Product, 2_2019_Category1Sales, 2_2019_Category2Sales, 4_2022_Category1Sales, 4_2022_Category2Sales, 1_2023_Category1Sales, 1_2023_Category2Sales, 7_2018_Category1Sales, 7_2018_Category2Sales
P001, 150, 231, 120,52,0,0,0,0
P002, 0,0,0,0,97,328,0,0
P003, 0,0,0,0,0,0,46,157

解决方案 #

Vertica可通过动态SQL拼接CASE语句实现需求，无需依赖固定的列定义，自动适配表中所有年月组合：

DO $$
DECLARE
  pivot_cols VARCHAR(MAX);
BEGIN
  -- 拼接所有动态列的CASE逻辑
  SELECT STRING_AGG(
    CONCAT(
      'MAX(CASE WHEN Month = ', Month, ' AND Year = ', Year, ' THEN Category1Sales ELSE 0 END) AS "', Month, '_', Year, '_Category1Sales", ',
      'MAX(CASE WHEN Month = ', Month, ' AND Year = ', Year, ' THEN Category2Sales ELSE 0 END) AS "', Month, '_', Year, '_Category2Sales"'
    ), ', '
  ) INTO pivot_cols
  FROM (SELECT DISTINCT Month, Year FROM ProductCategorySales) AS unique_dates;

  -- 执行动态生成的查询
  EXECUTE IMMEDIATE CONCAT(
    'SELECT Product, ', pivot_cols, ' FROM ProductCategorySales GROUP BY Product ORDER BY Product'
  );
END;
$$;

代码说明 #

1. 动态列生成：通过STRING_AGG将所有唯一年月组合对应的CASE语句拼接成列定义，每个年月生成两个列（分别对应Category1和Category2的销售数据），无数据时填充0。
2. 动态执行：用EXECUTE IMMEDIATE执行拼接后的SQL，自动适配表中新增的年月组合，无需手动修改代码。
3. 分组排序：最终结果按Product分组排序，每个产品对应一条记录，所有动态列按年月组合展示对应数据。

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