SQL PIVOT透视后去除null值 按指定格式重排数据方法

问题场景 #

当前数据透视后单条订单仅在对应Report列存在有效值，其余列为null，结果示例：

目标是按每3条数据为一组，将三列非空值合并到同一行，同时生成连续组号作为Serial_NO，预期输出：

原有语句问题 #

• 未提前给数据分配分组标识，透视后无法将3条关联数据合并到同一行
• 列名拼接存在多余括号，语法错误
• 未提取每行的非空Report值，也未生成要求的Serial_NO序号列

修正后SQL（SQL Server环境适用） #

核心逻辑是先给排序后的数据分配连续行号，通过行号计算所属分组、对应Report列，再做透视聚合：

SELECT 
  group_idx + 1 AS Serial_NO,
  Report1_ID,
  Report2_ID,
  Report3_ID
FROM (
  SELECT
    -- 每3条数据为1组，计算分组序号
    (ROW_NUMBER() OVER (ORDER BY Order_ID) - 1) / 3 AS group_idx,
    -- 提取当前行唯一的非空Report值
    COALESCE(Report1_ID, Report2_ID, Report3_ID) AS report_val,
    -- 标记当前值所属的Report列
    'Report' + CAST(((ROW_NUMBER() OVER (ORDER BY Order_ID) - 1) % 3 + 1) AS VARCHAR(10)) + '_ID' AS report_col
  FROM OrderTable
) src
PIVOT (
  MAX(report_val)
  FOR report_col IN (Report1_ID, Report2_ID, Report3_ID)
) pvt
ORDER BY Serial_NO

逻辑说明 #

• ROW_NUMBER() OVER (ORDER BY Order_ID)：按指定字段排序生成连续行号，如果需要按原order_number字段排序，替换ORDER BY后的字段即可
• (行号-1)/3：整数除法规则下，行号1-3计算结果为0、4-6为1、7-9为2，作为分组唯一标识，+1后就是需要的Serial_NO
• COALESCE函数用于取三列中的非空值，适配原始透视结果每行仅一个Report列有值的特点
• 最终透视会自动把同组的三个Report值聚合到同一行对应列，自动过滤null值

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