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