SQL - Set a Flag Based on a Conditions Based on Multiple IDs Working in SQL Server 2012 and trying to get the output below. I would like the Flag column (doesn't currently exist) to be 1 if the rows with the same primary ID have both Test A and B, and 0 otherwise. I manually entered the desired Flag values as an example. Does anyone know how to do this with SQL?



Any help would be greatly appreciated. Thanks!
Something like this may be

select t.*, 
  case sum(case when test in ('a','b') then 1 else 0 end) -- count occurrences of tests 'a' and 'b'
       over (partition by primary_id) -- for each primary_id
  when 2 then 1 else 0 end flag  -- if there are two, we're OK
from test t


fiddle