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

Answer 2

This is a pretty simple problem; you want to group rows by id and value, but grouping A and B values together:

select id, group_concat(distinct value order by value separator ' & ')
from mytable
group by id, case when value not in ('A','B') then value end

Or:

group by id, case value when 'B' then 'A' else value end

Anything that differentiates between non-A or B values but not between A and B will work.

This will eliminate duplicates; if you have duplicate id and value for some rows, you need to specify how you want those handled (in the case where they have value A or B as well as in the case where they don't).