How can I look for unique values while having duplicate ids?

I have a table that has ids that repeat. I want an id that has all three values (TWIX, HERSHYS, and M&MS). The values can be on different lines but I do not know how to find the id by using statements that go line by line rather then by groups.

I have tried (in Microsoft SQL server)

select *, 
case when 'Hershys' in (Col1, Ccol2) then 1 else 0 as case1,
case when 'TWIX' in (Col1, Col2) then 1 else 0 as case2,
case when 'M&M' in (Col1, Col2) then 1 else 0 as case3
into #candyflags_t1
from table 
    

   select * from #candyflags_t1
   where case1 > 0 and case2> 0 and case3 > 0

This does not work due to SQL going line by line. How should I go about either creating flags or querying this in order to find which id group has all three values?

+--------------------+----------+
|  ID     |    Col1    | Col2    |
+---------------------+----------+
| L123    |   TWIX     |         |
 +--------------------+----------+
| L123    |  Hershys   |         |
+--------------------+-----------+
| L123    |            |   m&ms  |
+--------------------+----------+
| F143    |            |   m&ms  |
+--------------------+----------+
| F143    |   Snickers | gummies |
+--------------------+----------+

In this table I would want to find L123 but not F143.

Answer

You can use group by and having. If you want the ids that have all three brands:

select id
from table t cross apply
     (values (col1), (col2)) v(col)
where col in ('Hershys', 'TWIX', 'M&M')
group by id
having count(distinct col) = 3;

cross apply is a convenient way of unpivoting the data, so you can deal with one column instead of two.

Advertisement

Answer