Select every rows that contains all elements of group

Column [TYPE] has A or B or C. I want to select list of names those have all A,B,C in the table.

Result

    -- dealing with count
    select name from tempso group by name 
    having count(*) = (select count(distinct type) from tempso);

    -- dealing with specifying elements
    select name from tempso group by name 
    having type in all('A', 'B', 'C');

Actually, I wanted to do this with second method because that TYPES A,B,C have subtypes so risk of duplicates but I got this error below.

Msg 156, Level 15, State 1, Line 10 syntax error 'all'...

Is there any way to do this?

Answer

You can use group by and having:

select name
from t
group by name
having count(*) = (select count(distinct type) from t);

This assumes that the name/type rows are not repeated in the table.

Edit:

If you just want to check for A/B/C, then:

select name
from t
where type in ('A', 'B', 'C')
group by name
having count(*) = 3;

Or:

having count(distinct type) = 3

if the table has duplicates.