Filter rows that share two particular values

Question

I have a table similar to what's below:

ID	VALUE
1	A
2	A
2	B
2	C
3	B

I want to return a single row when an ID has both A and B values in the VALUE column. The result I want would look like this:

ID	VALUE
1	A
2	A & B
2	C
3	B

How Do I combine those two rows into a single row?

This is the code I currently have

SELECT *
FROM TABLE
GROUP BY CASE WHEN VALUE IN(A) AND VALUE IN(B) THEN ID
ELSE 1 END;

However, it returns a blank table.

Answer 1

You can try this :

SELECT ID, 'A & B' as Value
FROM mytable
group by ID
HAVING COUNT(case when VALUE = 'A' then 1 end) + COUNT(case when VALUE = 'B' then 1 end)  = 2
UNION ALL
select t.*
from mytable t
left join (
  SELECT ID
  FROM mytable
  GROUP BY ID
  HAVING COUNT(case when VALUE = 'A' then 1 end) + COUNT(case when VALUE = 'B' then 1 end)  = 2
) as s on s.ID = t.ID and ( t.VALUE = 'A' or t.VALUE = 'B' )
where s.ID is null
order by ID

Result :

ID  Value
1   A
2   A & B
2   C
3   B

First select to select only IDs having both values 'A' and 'B', while second select to get all records exepts the ones already selected by the first select

Demo here