SQL Server PIVOT查询返回多行含NULL值，如何合并为单行？

问题根源 #

你的PIVOT查询返回多行且包含NULL值，核心原因是源查询（source子查询）中包含了dsf.Name as FieldName字段，但该字段未参与PIVOT的聚合或透视逻辑，SQL Server会自动将其作为分组字段。由于源数据中每条记录的FieldName各不相同，最终被拆分为3个独立分组，导致PIVOT输出3行，每行仅对应列有值，其余为NULL。

修正后的查询语句 #

删除源查询中多余的dsf.Name as FieldName字段，同时修正原查询中列别名与DataStoreFieldId含义不匹配的错误：

select 
    Casino as 'Casino', 
    [42] as 'Withdrawal per Month',
    [43] as 'Withdrawal per Week',
    [163] as 'Withdrawal Per Transaction' 
from
    (select 
         r.Casino, wf.WebsiteValue, wf.DataStoreFieldId
     from 
         Review r 
     inner join 
         WebsiteField wf on wf.ReviewId = r.Id
     inner join 
         DataStoreField dsf on dsf.Id = wf.DataStoreFieldId 
     where 
         dsf.Id in (42, 43, 163) 
         and Casino = 'Tusk Casino' 
         and WebsiteValue like '%USD%') as source
pivot
    (MAX(WebsiteValue)  
     FOR DataStoreFieldId IN ([42], [43], [163])  
    ) AS PivotTable
where 
    Casino = 'Tusk Casino' 
order by 
    Casino

预期输出结果 #

Casino       Withdrawal per Month  Withdrawal per Week  Withdrawal Per Transaction
------------ -------------------- --------------------- --------------------------
Tusk Casino  EUR/USD 16,000       EUR/USD 4,000         EUR/USD 1,000

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