如何通过PIVOT聚合合并消除NULL值 按年月分组输出销售统计结果

问题原因 #

直接对原AgentSales表执行PIVOT时，PIVOT运算符默认将源表中未出现在聚合函数、FOR子句中的所有字段作为分组维度。原表包含唯一标识id字段，会导致最终按id分组生成多行记录，进而出现同个年月对应多条数据、大量NULL值的问题。

方案1：先聚合再PIVOT（适配现有写法） #

先通过子查询按年月、代理分组统计销售额，排除id等不需要的字段后再做行转列，即可实现单年月单条记录的效果，可通过ISNULL将无销量的NULL值替换为0：

SELECT 
  YRMON,
  ISNULL([Agent A], 0) AS [Agent A],
  ISNULL([Agent B], 0) AS [Agent B]
FROM (
  -- 先完成分组统计，仅保留需要的三个字段
  SELECT 
    CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON, 
    AGENT, 
    SUM(SALES) SALES
  FROM AgentSales
  GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR), AGENT
) AS t
PIVOT (
  SUM(SALES) 
  FOR AGENT IN ([Agent A], [Agent B])
) AS PIVOTTABLE
ORDER BY YRMON

方案2：条件聚合写法（跨数据库通用） #

不依赖PIVOT语法，兼容性更强，MySQL、PostgreSQL等多数数据库都支持：

SELECT 
  CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR) YRMON,
  SUM(CASE WHEN AGENT = 'Agent A' THEN SALES ELSE 0 END) AS [Agent A],
  SUM(CASE WHEN AGENT = 'Agent B' THEN SALES ELSE 0 END) AS [Agent B]
FROM AgentSales
GROUP BY CAST(YEAR([DATE]) AS VARCHAR)+' '+CAST(MONTH([DATE]) AS VARCHAR)
ORDER BY YRMON

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