sql - 如何用相同 PolicyNumber 的非空值替换空字符串

Question

目标是将 ClaimType 列中的空值替换为每个 CoverageType 和 PolicyNumber 的非空值

出于某种原因，我编写的 SELECT 语句没有给我理想的结果。在我指定的 WHERE 子句中:

 where  
             PolicyNumber = c.PolicyNumber 
            and CoverageType = c.CoverageType  

但为什么对于 PolicyNumber 00002 我有 CPL CoverageType 的“Occurance”？

我希望看到 Occurrence-Pollution:

我在这里错过了什么？

declare @ClaimType table (PolicyNumber varchar(50), QuoteID int,  CoverageType varchar(50), ClaimType varchar(100))

insert into @ClaimType values   ('00001',1, 'CGL', ' '),
                                ('00001',2, 'CGL', 'Occurrence'),
                                ('00002',1, 'CPL', ' '),
                                ('00002',2, 'CPL', 'Occurrence-Pollution')

select PolicyNumber,
        QuoteID,
        CoverageType,
        ClaimType,
        case when ClaimType = ' ' then (
                                        select top 1 ClaimType  
                                        from @ClaimType c 
                                        where   
                                                 PolicyNumber = c.PolicyNumber 
                                                and CoverageType = c.CoverageType 
                                        order by QuoteID desc
                                        ) 
                                        else ClaimType End as ClaimType1
from @ClaimType
go

Answer 1

这是你想要的吗？

select ct.*,
       coalesce(nullif(ltrim(ClaimType), ''),
                max(ClaimType) over (partition by PolicyNumber, CoverageType)
               ) as ClaimType_2
from @ClaimType ct;