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

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?

CodePudding user response：

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