SQL Server中按指定逻辑填充PIVOT表的NULL值

针对你提到的PIVOT表NULL值填充规则（优先取左侧最近非NULL值，左侧无值则取右侧最近非NULL值），分版本提供解决方案：

方案一：SQL Server 2022及以上版本（支持IGNORE NULLS） #

利用LAST_VALUE窗口函数结合IGNORE NULLS参数，高效实现前向+反向填充：

WITH ForwardFilled AS (
    SELECT 
        ID,
        LAST_VALUE(Col1) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) IGNORE NULLS AS Col1_Forward,
        LAST_VALUE(Col2) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) IGNORE NULLS AS Col2_Forward,
        LAST_VALUE(Col3) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) IGNORE NULLS AS Col3_Forward,
        LAST_VALUE(Col4) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) IGNORE NULLS AS Col4_Forward
    FROM YourPivotTable
),
BackwardFilled AS (
    SELECT 
        ID,
        COALESCE(Col1_Forward, LAST_VALUE(Col1_Forward) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) IGNORE NULLS) AS Col1_Final,
        COALESCE(Col2_Forward, LAST_VALUE(Col2_Forward) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) IGNORE NULLS) AS Col2_Final,
        COALESCE(Col3_Forward, LAST_VALUE(Col3_Forward) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) IGNORE NULLS) AS Col3_Final,
        COALESCE(Col4_Forward, LAST_VALUE(Col4_Forward) OVER (PARTITION BY ID ORDER BY (SELECT NULL) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) IGNORE NULLS) AS Col4_Final
    FROM ForwardFilled
)
SELECT * FROM BackwardFilled;

• 替换YourPivotTable为你的实际表名，Col1/Col2等为PIVOT后的列名
• ORDER BY (SELECT NULL)确保列的顺序与原表一致，需根据实际列顺序调整

方案二：SQL Server 2022以下版本（不支持IGNORE NULLS） #

用递归CTE实现前向填充，再反向填充处理左侧全NULL的情况：

-- 替换YourPivotTable、ID、Col1-Col4为实际表名和列名
WITH RecursiveFill AS (
    SELECT 
        ID,
        Col1,
        Col2,
        Col3,
        Col4,
        1 AS Step
    FROM YourPivotTable
    UNION ALL
    SELECT 
        ID,
        Col1,
        COALESCE(Col2, Col1) AS Col2,
        COALESCE(Col3, Col2) AS Col3,
        COALESCE(Col4, Col3) AS Col4,
        Step + 1
    FROM RecursiveFill
    WHERE Col2 IS NULL OR Col3 IS NULL OR Col4 IS NULL
),
ForwardFilled AS (
    SELECT 
        ID,
        Col1,
        Col2,
        Col3,
        Col4
    FROM RecursiveFill
    WHERE Step = (SELECT MAX(Step) FROM RecursiveFill rf WHERE rf.ID = RecursiveFill.ID)
),
ReverseRecursiveFill AS (
    SELECT 
        ID,
        Col1,
        Col2,
        Col3,
        Col4,
        1 AS Step
    FROM ForwardFilled
    UNION ALL
    SELECT 
        ID,
        COALESCE(Col1, Col2) AS Col1,
        COALESCE(Col2, Col3) AS Col2,
        COALESCE(Col3, Col4) AS Col3,
        Col4,
        Step + 1
    FROM ReverseRecursiveFill
    WHERE Col1 IS NULL OR Col2 IS NULL OR Col3 IS NULL
),
FinalFilled AS (
    SELECT 
        ID,
        Col1,
        Col2,
        Col3,
        Col4
    FROM ReverseRecursiveFill
    WHERE Step = (SELECT MAX(Step) FROM ReverseRecursiveFill rf WHERE rf.ID = ReverseRecursiveFill.ID)
)
SELECT * FROM FinalFilled;

• 若列数更多，需在递归步骤中依次添加对应列的填充逻辑

注意事项 #

• 确保ID是每行的唯一标识，用于按行分组处理
• 若PIVOT后的列顺序与示例不同，需调整窗口函数或递归中的列处理顺序

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