SQL filter group based on occurrence of attribute

Question

Is it possible in (T-)SQL to filter groups of records by the occurrence of a specific value, like this question about a Python dataframe?

For instance, out of the following table I want the full groups as output that contain 16 as Value.

ID Group Value
1    1   10
2    1   12
3    1   16
4    2   10
5    2   14
6    2   18
7    3   12
8    3   16
9    3   18

The resulting output I want is

ID Group Value
1    1   10
2    1   12
3    1   16
7    3   12
8    3   16
9    3   18

According to the documentation and to the errors I get, GROUP BY or PARTITION BY and HAVING works only when summing the total value.

Answer 1

You could use EXISTS:

SELECT *
FROM tab t1
WHERE EXISTS (SELECT 1
              FROM tab t2
              WHERE t1.[group] = t2.[group]
                AND t2.[Value] = 16)