SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



create table chat (touser int, fromuser int, message varchar(100), messagetime int);
insert into chat( touser, fromuser, message, messagetime)
values (1, 10, 'hi', 1), (8, 1, 'yes', 2), (2, 8, 'blah', 3), (10, 1, 'test', 4),
  (1, 10, 'anything', 5), (9, 4, 'hello', 6), (2, 3, 'hi', 7);
7 rows affected

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;


lesser
greater
msg


2
3
hi

1
8
yes

2
8
blah

4
9
hello

1
10
hi -> test -> anything