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.
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).
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
