SQL Server中PIVOT函数为何输出多行数据？

问题背景 #

使用CASE+MAX实现透视能得到单行预期结果，但用PIVOT关键字却输出两行（一行含NULL值，一行结果正确）。核心疑问：

• 未参与透视的ENTRY_Lang_EN字段（一个为空字符串，一个为NULL）为何会导致多行输出？
• PIVOT命令的底层逻辑是什么？
• 如何用不含PIVOT的SQL（GROUP BY+CASE+MAX）实现预期结果？

预期结果的查询 #

;WITH My_Data AS 
(
    SELECT  
        '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' AS ENTRY_UID  
        ,'Entry 1' AS ENTRY_Lang_DE
        ,'' AS ENTRY_Lang_EN

    UNION ALL 

    SELECT  
        '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' AS ENTRY_UID  
        ,'Entry 2' AS ENTRY_Lang_DE
        ,NULL AS ENTRY_Lang_EN
)
SELECT
    MAX(CASE WHEN ENTRY_UID = '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' THEN ENTRY_Lang_DE END) AS [4A8C72D8-F02A-44E4-8E5A-23451CB436B1],
    MAX(CASE WHEN ENTRY_UID = '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' THEN ENTRY_Lang_DE END) AS [5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E]
FROM 
(
    SELECT
         ENTRY_UID,
         ENTRY_Lang_DE
        ,ENTRY_Lang_EN
    FROM My_Data 
) AS T_My_Data 
;

PIVOT实际输出的查询 #

;WITH My_Data AS 
(
    SELECT  
        '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' AS ENTRY_UID  
        ,'Entry 1' AS ENTRY_Lang_DE
        ,'' AS ENTRY_Lang_EN

    UNION ALL 

    SELECT  
        '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' AS ENTRY_UID  
        ,'Entry 2' AS ENTRY_Lang_DE
        ,NULL AS ENTRY_Lang_EN
)
SELECT pvt.* FROM My_Data 

PIVOT 
( 
    MAX( ENTRY_Lang_DE ) 
    FOR ENTRY_UID IN 
    ( 
         [4A8C72D8-F02A-44E4-8E5A-23451CB436B1] 
        ,[5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E]
    ) 
) AS pvt

问题解答 #

1. 为何ENTRY_Lang_EN导致PIVOT输出多行？ #

PIVOT的底层会自动将所有未出现在PIVOT子句中的字段作为分组依据。你的查询里，ENTRY_Lang_EN没有被包含在PIVOT的聚合或行转列逻辑中，SQL会按ENTRY_Lang_EN的值分组：空字符串和NULL是两个不同的分组值（SQL中NULL不等于任何值，包括另一个NULL），因此最终输出两行。

而你用CASE+MAX的查询未指定GROUP BY，默认对全表聚合，所以只会得到一行结果。

2. PIVOT的底层逻辑 #

PIVOT本质是GROUP BY+条件聚合的语法糖，执行步骤如下：

• 确定分组字段：所有未出现在PIVOT(聚合函数(列) FOR 行转列字段 IN (...))中的字段，都会作为GROUP BY的分组键。
• 对每个分组，按FOR子句指定的行转列字段值，执行聚合函数计算，将行值转为列名。
• 输出每个分组对应的聚合结果行。

如果数据源中分组字段存在不同值，就会生成多行结果。

3. 用GROUP BY+CASE+MAX实现预期结果 #

你的原始CASE+MAX查询已经是正确实现，因为它对全表做聚合，直接得到单行结果。如果需要显式模拟PIVOT逻辑但避免分组问题，可以用常量分组确保只有一个分组：

;WITH My_Data AS 
(
    SELECT  
        '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' AS ENTRY_UID  
        ,'Entry 1' AS ENTRY_Lang_DE
        ,'' AS ENTRY_Lang_EN

    UNION ALL 

    SELECT  
        '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' AS ENTRY_UID  
        ,'Entry 2' AS ENTRY_Lang_DE
        ,NULL AS ENTRY_Lang_EN
)
SELECT
    MAX(CASE WHEN ENTRY_UID = '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' THEN ENTRY_Lang_DE END) AS [4A8C72D8-F02A-44E4-8E5A-23451CB436B1],
    MAX(CASE WHEN ENTRY_UID = '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' THEN ENTRY_Lang_DE END) AS [5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E]
FROM My_Data
-- 用常量分组确保仅生成一个结果行
GROUP BY (SELECT 1);

如果要修正你的PIVOT查询使其输出单行，只需排除不需要的ENTRY_Lang_EN字段，避免它成为分组键：

;WITH My_Data AS 
(
    SELECT  
        '4A8C72D8-F02A-44E4-8E5A-23451CB436B1' AS ENTRY_UID  
        ,'Entry 1' AS ENTRY_Lang_DE
        ,'' AS ENTRY_Lang_EN

    UNION ALL 

    SELECT  
        '5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E' AS ENTRY_UID  
        ,'Entry 2' AS ENTRY_Lang_DE
        ,NULL AS ENTRY_Lang_EN
)
SELECT pvt.* 
FROM (
    -- 排除ENTRY_Lang_EN，避免作为分组依据
    SELECT ENTRY_UID, ENTRY_Lang_DE FROM My_Data
) AS T_My_Data
PIVOT 
( 
    MAX(ENTRY_Lang_DE) 
    FOR ENTRY_UID IN 
    ( 
         [4A8C72D8-F02A-44E4-8E5A-23451CB436B1] 
        ,[5688ABC1-D2C5-4FE7-9E0A-6195CFF0563E]
    ) 
) AS pvt;

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