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!

What happened to ID's 2 and 3, with "Hi" and "how are you?" in the first result set? — Thom A, Mar 04 '21 at 10:57
The conversation is between Id 1 and 3 and there are 3 messages between them (with Id 2,3,4) so the result set should have the latest message. — Amaterasu666, Mar 04 '21 at 11:01
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) If not, why not? — Thom A, Mar 04 '21 at 11:02
I tried the above approach but the result set is different. I have attached and edited my result set in the query. I should be getting a single row value (latest one) for conversation between 2 Id's (here 1 & 3). Even if I partition it by both To & From result set remains the same.Things are getting trickier due to dependency on 2 columns i.e. To and From. — Amaterasu666, Mar 04 '21 at 11:27

score 0 · Accepted Answer · answered Mar 04 '21 at 11:22

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;

score 0 · Answer 2 · answered Mar 04 '21 at 11:50

SQL Server allows you to do this without a case expressions by unpivoting the data and then using window functions:

select t.*
from (select t.*,
             row_number() over (partition by v.user_other order by t.conversationTime desc) as seqnum
      from t cross apply
           (values (t.to, t.from), (t.from, to.to)
           ) v(user, user_other)
      where v.user = 1
     ) t
where seqnum = 1;

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

2 Answers2