If ID has both values A and B it should return AB

INPUT:

ID  VALUES
1    A
1    B
2    A
3    B

OUTPUT:

ID   VALUES
1    AB
1    AB
2    A
3    B

If ID has both values A and B it should return AB.

Answer

You are describing string aggregation as a window function. In standard ANSI SQL, the syntax would be:

select id, listagg(values) within group(order by values) over(partition by id) values
from mytable

Not all databases support this syntax though. In MySQL, you would use group_concat(), in Postgres, that’s string_agg(), and so on – but the logic remains the same.