SQL Server多值CTE执行异常原因及问题解惑

感谢Thom A针对「非空列重排为有序列」场景提供的解决方案。

我测试过程中发现一个问题：当仅查询单行数据时，以下代码结果符合预期：

WITH RNs AS
(
    SELECT 
        V.YourIDColumn,
        U.I,
        U.Col,
        ROW_NUMBER() OVER (ORDER BY IIF(U.Col IS NULL, 1, 0), U.I) AS RN
    FROM 
        (VALUES (1, null, 'pink', 'red', null, null, 'orange')) V (YourIDColumn, ColA, ColB, ColC, ColD, ColE, ColF)
    CROSS APPLY 
        (VALUES (1, ColA), (2, ColB), (3, ColC), (4, ColD),
                (5, ColE), (6, ColF)) U (I, Col)
)
SELECT 
    YourIDColumn,
    MAX(CASE RN WHEN 1 THEN Col END) AS ColA,
    MAX(CASE RN WHEN 2 THEN Col END) AS ColB,
    MAX(CASE RN WHEN 3 THEN Col END) AS ColC,
    MAX(CASE RN WHEN 4 THEN Col END) AS ColD,
    MAX(CASE RN WHEN 5 THEN Col END) AS ColE,
    MAX(CASE RN WHEN 6 THEN Col END) AS ColF
FROM
    RNs
GROUP BY 
    YourIDColumn;

但查询多行数据时，结果不符合预期：

WITH RNs AS
(
    SELECT 
        V.YourIDColumn,
        U.I,
        U.Col,
        ROW_NUMBER() OVER (ORDER BY IIF(U.Col IS NULL, 1, 0), U.I) AS RN
    FROM 
        (VALUES (1, NULL, 'pink', 'red', NULL, NULL, 'orange'), 
                (2, 'yellow', NULL, NULL, 'green', NULL, 'black')) V (YourIDColumn, ColA, ColB, ColC, ColD, ColE, ColF)
    CROSS APPLY 
        (VALUES (1, ColA), (2, ColB), (3, ColC), (4, ColD),
                (5, ColE), (6, ColF)) U (I, Col)
)
SELECT 
    YourIDColumn,
    MAX(CASE RN WHEN 1 THEN Col END) AS ColA,
    MAX(CASE RN WHEN 2 THEN Col END) AS ColB,
    MAX(CASE RN WHEN 3 THEN Col END) AS ColC,
    MAX(CASE RN WHEN 4 THEN Col END) AS ColD,
    MAX(CASE RN WHEN 5 THEN Col END) AS ColE,
    MAX(CASE RN WHEN 6 THEN Col END) AS ColF
FROM
    RNs
GROUP BY 
    YourIDColumn;

问题原因 #

核心问题是窗口函数缺少分区（PARTITION BY）子句：

• 单行数据时，ROW_NUMBER()默认对所有展开后的记录（仅当前行的6条拆分记录）排序，RN生成逻辑正常，非空列优先排序，最终分组后能正确映射到目标列。
• 多行数据时，ROW_NUMBER()没有按YourIDColumn分区，导致所有行的拆分记录被合并在一起排序生成RN。比如第一行的非空列和第二行的非空列会被统一分配RN值，分组后每个ID下的RN值混乱，无法对应正确的列位置。

关键知识点总结 #

• 窗口函数分区的必要性：处理多行独立数据时，必须用PARTITION BY指定分组维度，确保窗口计算在每个分组内独立进行，避免跨分组的干扰。
• 行展开与分组计算的配合：CROSS APPLY (VALUES ...)将单行多列拆分为多行后，若要对每行的拆分结果单独处理，必须配合窗口分区锁定处理范围。
• 空值排序逻辑的有效性：IIF(U.Col IS NULL, 1, 0)通过给空值赋予更大的排序值，实现非空列优先排列的逻辑，本身是合理的，但必须限定在单条记录的拆分范围内才能生效。

修正后的代码 #

在ROW_NUMBER()中添加PARTITION BY V.YourIDColumn即可解决问题：

WITH RNs AS
(
    SELECT 
        V.YourIDColumn,
        U.I,
        U.Col,
        ROW_NUMBER() OVER (PARTITION BY V.YourIDColumn ORDER BY IIF(U.Col IS NULL, 1, 0), U.I) AS RN
    FROM 
        (VALUES (1, NULL, 'pink', 'red', NULL, NULL, 'orange'), 
                (2, 'yellow', NULL, NULL, 'green', NULL, 'black')) V (YourIDColumn, ColA, ColB, ColC, ColD, ColE, ColF)
    CROSS APPLY 
        (VALUES (1, ColA), (2, ColB), (3, ColC), (4, ColD),
                (5, ColE), (6, ColF)) U (I, Col)
)
SELECT 
    YourIDColumn,
    MAX(CASE RN WHEN 1 THEN Col END) AS ColA,
    MAX(CASE RN WHEN 2 THEN Col END) AS ColB,
    MAX(CASE RN WHEN 3 THEN Col END) AS ColC,
    MAX(CASE RN WHEN 4 THEN Col END) AS ColD,
    MAX(CASE RN WHEN 5 THEN Col END) AS ColE,
    MAX(CASE RN WHEN 6 THEN Col END) AS ColF
FROM
    RNs
GROUP BY 
    YourIDColumn;

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