Flag items not having the specified combination in a column

I need to flag ID#s not having a specified combinations of DC and Storage Locations. For example:

Articles must have the following combinations for each DC:

DC	Storage Location
DC01	ABC, BCA, DCA
DC02	ABC, BCA
DC03	ABC, DCA

My desired outcome would be the below as I’d like to show the Storage Location missing.

ID#	DC#	Storage Location	Flag
1	DC02	ABC	Good
1	DC02	BCA	Good
2	DC01	ABC	Bad
2	DC01	BCA	Bad

The bad entries are because of the missing ‘DCA’ entry. So far I have the below however I can’t figure out how to count the number of matches per ID (i.e. must be 3 for DC01 so that the flag would be Good else Bad). Once the flag is figured out the only thing I can think of is to do a union (one select statement for each combination) – is there a more optimized way to do this?

select
ID,
DC,
Storage_Location

FROM table
where 
DC = 'DC01' AND Storage_location in ('ABC', 'BCA', 'DCA')
group by ID, DC, Storage_location

The table contain multiple ID, and each ID having different combinations:

ID#	DC#	Storage Location
1	DC02	ABC
1	DC02	BCA
2	DC01	ABC
2	DC01	BCA
3	DC03	ABC
3	DC03	DCA

Answer

You can use STRING_AGG to see whether data is complete. For instance:

select
  id, dc, location,
  case when
  (
     select t.dc + ':' + string_agg(t2.location, ',') within group (order by t2.location)
     from mytable t2
     where t2.id = t.id and t2.dc = t.dc
  ) in ('DC01:ABC,BCA,DCA', 'DC02:ABC,BCA', 'DC03:ABC,DCA') then
    'GOOD'
  else
    'BAD'
  end as status
from mytable t
order by id, dc, location;

The subquery is necessary, because there exists no analytic version of STRING_AGG yet in SQL Server.