SQL - Set a Flag Based on a Conditions Based on Multiple IDs

Question

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!

Answer 1

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

Answer 2

If there is the possibility that the same test can be recorded against the same primary ID more than once, then you need to be a bit careful to ensure you pick up both tests having been done, rather than just one having been done twice. So, for example:

SELECT t.[Primary ID], t.[Secondary ID], t.Test, Flag =
    CASE
        WHEN NOT EXISTS (SELECT * FROM tests a WHERE a.[Primary ID] = t.[Primary ID] AND a.Test = 'A') THEN 0
        WHEN NOT EXISTS (SELECT * FROM tests b WHERE b.[Primary ID] = t.[Primary ID] AND b.Test = 'B') THEN 0
        ELSE 1
    END
FROM tests t;

Or, using joins rather than sub-queries:

SELECT t.[Primary ID], t.[Secondary ID], t.Test,
    Flag = CASE WHEN a.Test IS NOT NULL AND b.Test IS NOT NULL THEN 1 ELSE 0 END
FROM tests t
LEFT JOIN (
    SELECT DISTINCT [Primary ID]
    FROM tests
    WHERE Test = 'A'
) a
ON a.[Primary ID] = t.[Primary ID]
LEFT JOIN (
    SELECT DISTINCT [Primary ID]
    FROM tests
    WHERE Test = 'B'
) b
ON b.[Primary ID] = t.[Primary ID]