含空值多字段SQL Pivot透视查询简化实现方案咨询

核心思路 #

放弃原生PIVOT语法，改用条件聚合实现，同时用MAX/MIN替代SUM聚合解决NULL丢失问题。
因为你的业务逻辑保证了每个(ParentId, Type)组合仅存在一行数据，MAX(字段)返回的结果和字段原值完全一致，不会修改NULL值，完美解决SUM聚合可能遇到的NULL丢失问题。

实现代码 #

你可以直接把原有非透视查询作为CTE子查询，外层套条件聚合逻辑即可，不需要修改原有基础查询：

WITH BaseData AS (
    -- 直接复用你已有的非透视查询逻辑
    Select p.Id As ParentId, t.Type, c1.Value As Value, c1.Count, c2.Value As AdditionalValue
    From TypeTable as t
    Left Join Parent as p On t.Id = p.TypeId And Filter1 In @listFilter1 And Filter2 In @listFilter2
    Left Join FirstChild As c1 On (c1.ValidFrom <= @ValidOn And c1.ValidTo >= @ValidOn) And c1.ParentId = p.Id
    Left Join (Select * From SecondChild Where Id In (Select Min(Id) From SecondChild Group By FirstChildId)) As c2 On c2.FirstChildId = c1.Id
    Where t.Id In(/*你的Type Id列表*/)  
)
SELECT 
    ParentId,
    -- Type1对应的3个透视列
    MAX(CASE WHEN Type = 'Type1' THEN Value END) AS Type1Value,
    MAX(CASE WHEN Type = 'Type1' THEN Count END) AS Type1Count,
    MAX(CASE WHEN Type = 'Type1' THEN AdditionalValue END) AS Type1AdditionalValue,
    -- Type2对应的3个透视列
    MAX(CASE WHEN Type = 'Type2' THEN Value END) AS Type2Value,
    MAX(CASE WHEN Type = 'Type2' THEN Count END) AS Type2Count,
    MAX(CASE WHEN Type = 'Type2' THEN AdditionalValue END) AS Type2AdditionalValue,
    -- 剩余73个Type按上述规则动态生成即可
    -- 后续新增透视字段时，仅需在每个Type块中加一行对应的CASE语句即可
FROM BaseData
GROUP BY ParentId

方案优势 #

• 完美保留NULL值：CASE匹配不到对应Type、或者原字段值为NULL时，返回结果均为NULL，完全符合需求
• 多字段扩展成本极低：后续要新增到7个透视字段，仅需在每个Type对应的代码块中新增对应CASE语句即可，逻辑清晰无冗余
• 动态生成方便：只需遍历你需要的Type列表，批量生成对应CASE代码块即可，不用处理多次PIVOT的复杂关联逻辑
• 性能更优：仅需对基础查询结果做一次分组聚合，相比多次PIVOT后关联的方案，减少了大量表扫描和关联开销

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