How to get rows that have Null and values on zkfeature both based on same zplid and same codetypeid ?

Question

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 code

typeid 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

Answer 1

yes this is correct but if i need to get it as summarize like that

CodeTypeId    Zplid    countfeaturekey
 849774         25820          4

so How i do that as above summarize result

Answer 2

Check a direct approach:

select * from #gen
where CodeTypeId in ( select CodeTypeId from #gen group by CodeTypeId having count(distinct Zplid) = 1 )
and CodeTypeId in ( select CodeTypeId from #gen where Zfeaturekey is null)
and CodeTypeId in ( select CodeTypeId from #gen where Zfeaturekey is not null)