Merge Multiple Rows to One Row having Same value swapped between 2 columns In SQL Server

Question

I am working on an API-based simple chat module. I am trying to get chat conversations for a particular user but due to 2 columns having the same value swapped between each other is causing my data to be duplicated.

I want to merge rows having the same values swapped between 2 columns and the merged row should be based on the latest entry inserted in the database.

The data looks like this :

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
2   3    1     hi                 12:00PM
3   1    3     how are you?       12:15PM
4   3    1     I am fine.         12:30PM
5   4    5     Hi!                04:30PM
6   5    4     Hello              04:35PM
7   1    5     Hola!              06:30PM

So for example if user with Id 1 My result needs to look like this:

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
4   3    1     I am fine.         12:30PM
7   1    5     Hola!              06:30PM

If Id is 5 then result would be like this:

Id  To  From   Message        ConversationTime
6   5    4     Hello              04:35PM
7   1    5     Hola!              06:30PM

My result set looks like this:

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
3   1    3     how are you?       12:15PM
4   3    1     I am fine.         12:30PM
7   1    5     Hola!              06:30PM

Any help would be grateful. Thanks in advance!

Answer 1

The idea is the same as the linked duplicate Get top 1 row of each group ; just use a CASE expression to get the ID of the other user:

DECLARE @ID int = 1;
WITH RNs AS(
    SELECT ID,
           [To], --TO is a reserved keyword and should not be used for object names
           [From], --FROM is a reserved keyword and should not be used for object names
           Message,
           ConversationTime, --I assume this is a time
           ROW_NUMBER() OVER (PARTITION BY CASE [To] WHEN @ID THEN [From] ELSE [To] END ORDER BY ConversationTime DESC) AS RN --TO and FROM are reserved keywords and should not be used for object names
    FROM dbo.YourTable
    WHERE @ID IN ([To],[From])) --TO and FROM are reserved keywords and should not be used for object names
SELECT ID,
       [To], --TO is a reserved keyword and should not be used for object names
       [From], --FROM is a reserved keyword and should not be used for object names
       Message,
       ConversationTime --I assume this is a time
FROM RN
WHERE RN = 1;