Grouping by two columns where the value in column A and B are the same

August 15, 2022

Suppose that I have this sql table

Table: Chat

+---------+----------+----------+
| ToUser  | FromUser | Message  |
+---------+----------+----------+
| 1       |       10 | hi       |
| 8       |        1 | yes      |
| 2       |        8 | blah     |
| 10      |        1 | test     |
| 1       |       10 | anything |
| 9       |        4 | hello    |
| 2       |        3 | hi       |
+---------+----------+----------+

How can I group by ToUser and FromUser where it considers the rows where ToUser = 1 and FromUser = 10 are in the same group of the rows ToUser = 10 and FromUser = 1

I tried the following:

select ToUser, FromUser
from Chat
group by ToUser, FromUser

but this did not help, it considers them different groups.

How can I solve this issue?

>Solution :

You want (a,b) and (b,a) be treated as the same pair. This is easily done by grouping by the lesser and the greater of the two:

select 
  case when touser < fromuser then touser else fromuser end as lesser,
  case when touser > fromuser then touser else fromuser end as greater,
  string_agg(message, ' -> ') within group (order by messagetime) as msg
from chat
group by
  case when touser < fromuser then touser else fromuser end,
  case when touser > fromuser then touser else fromuser end;

As of SQL Server 2022 you can use GREATEST and LEAST instead for readbility.

Demo: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c99ccaa9782253baa1a19689dcdcdba4