How to get zplid that have zfeature key value on all rows?

Question

Exactly I need zplid and codetypeid that have feature value
on all rows .

if I have Null on zfeature key on one row then I don't Need it .

I need to get zplid that have features Not Null on all rows as zplid 9714
but 9823,5890 have Null so I don't Need it

this is my sample data

create table #gen
(
zplid int,
CodetypId int,
ZfeatureKey nvarchar(20)
)
insert into #gen(zplid,CodetypId,ZfeatureKey)
values
(9714,849774,1501290046),
(9714,849774,1501099991),
(9714,849774,1501555555),
(9823,9732410,1501290046),
(9823,9732410,1501099991),
(9823,9732410,NULL),
(5890,838651,1501290046),
(5890,838651,NULL),
(5890,838651,NULL)
expected result is

zplid CodetypId ZfeatureKey
9714 849774 1501290046
9714 849774 1501099991
9714 849774 1501555555

I tried to do as below :

select zplid,CodetypId,ZfeatureKey
from #gen
where ZfeatureKey is not null

but it give me zplid and codetypeid that have values ON Zfeature key on all rows of all zplid
and this I don't need

Answer 1

I am not sure that this is the most efficient query, but it does the job:

; WITH CTE AS (
   SELECT zplid, CodetypId, ZfeatureKey, 
          totalcnt = COUNT(*) OVER (PARTITION BY zplid, CodetypId),
          nonnullcount = COUNT(ZfeatureKey) OVER(PARTITION BY zplid, CodetypId)
   FROM #gen
)
SELECT zplid, CodetypId, ZfeatureKey 
FROM   CTE
WHERE  totalcnt = nonnullcount

Answer 2

Hi @ahmed salah ,

Please also refer below:

;with cte as (  
 select zplid,  
 sum(iif(Zfeaturekey is null,1,0)) sumnull  
 from #gen group by Zplid)  
 select * from #gen  
 where Zplid in (select Zplid from cte where sumnull=0)  

Output:

zplid	CodetypId	ZfeatureKey  
9714	849774	1501290046  
9714	849774	1501099991  
9714	849774	1501555555  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table