SQLTeam.com | Weblogs | Forums

How to get rows that have same zplid and same codetypeid and have both Null and Values on zfeature key?

I work on SQL server 2012 . I face issue I can't get rows from table #gen when it have Null and values on

zfeaturekey based on zplid and codetypeid .

meaning I need to get rows that have NULL ON zfeaturekey and Values on Zfeaturekey

but must be same codetypeid and same zplid .

 create table #gen
         (
         CodeTypeId int,
         Zplid  int,
         Zfeaturekey nvarchar(50)
         )
         insert into #gen values
 (854838,25820,NULL),
 (849774,25820,1502260001),
 (849774,25820,1502260001),
 (849774,25820,1502260016),
 (849774,25820,NULL),
 (987431,26777,1502270003),
 (987431,26777,1502280005),
 (987431,26777,1502290001)

Expcted Result :

 CodeTypeId    Zplid    Zfeaturekey
 849774    25820    1502260001
 849774    25820    1502260001
 849774    25820    1502260016
 849774    25820    NULL

i will not get codetypeid 854838 and zplid 25820 because it have NULL Only on zfeaturekey
i will not get codetypeid 987431 and zplid 26777 because it Not have NULL ON zfeaturekey

;with cte_eligible_values as (
    select codetypeid, zplid
    from #gen
    group by codetypeid, zplid
    having max(case when Zfeaturekey is null then 1 else 0 end) = 1 and
        max(case when Zfeaturekey is null then 0 else 1 end) = 1
)
select g.*
from cte_eligible_values cev
inner join #gen g on g.CodeTypeId = cev.CodeTypeId and g.Zplid = cev.Zplid

thanks solved