CODEWITHSUNDEEP
sql-server
Edit
Edit

Reputation: 393

Merge multiple rows into one row using SQL Server?

I want to merge my notification data using a stored procedure in SQL Server. I have written the query, but in this query I am getting more than user data in multiple rows.

I want to merge follower notifications. like "User a follow user b then user b" get notification like "user a started follow you". Again "user c follow user b" then "user b again received notification like" "user c follow you".

Again "user d follow again user b then user b same like get notification". But now I want to merge like this notification "User a,b and 1 more user started follow you".

Here is my stored procedure:

SELECT 
    N.NotificationId,
    N.UserId,
    N.ActionUserId,
    (CASE WHEN N.NotificationTypeId = 1 THEN 1
          WHEN N.NotificationTypeId = 7 THEN 3
        ELSE    
             2
    END) AS TypeId,                         
    AU.ProfileImage,
    AU.UserName,
    N.IsRead,
    (CASE WHEN N.NotificationTypeId = 1 THEN 1
        WHEN N.NotificationTypeId = 7 THEN 3
      ELSE 2
    END) AS TypeId,         
    N.NotificationTypeId,
    N.InsertDateTime
FROM 
    Notifications N
INNER JOIN 
    Users U ON N.UserId = U.UserId
INNER JOIN 
    Users AU ON N.ActionUserId = AU.UserId      
ORDER BY 
    N.InsertDateTime DESC

This is my current o/p =>

  NotificationId | UserId | ActionUserId | UserName | NotificationTypeId | InsertDateTime            | ProfileImage 
  6                20        15             hbc        1                    2017-06-22 17:14:16.803    20170416032403869.jpeg
  5                20        16             tyu        1                    2017-06-22 17:12:12.297     20170416031522534.jpeg
  4                20        17             opl        1                    2017-06-22 17:11:58.060     20170416031250102.jpeg
  3                10        11             abc        1                    2017-06-22 16:14:16.803    20170416032403867.jpeg
  2                10        12             xyz        1                    2017-06-22 16:14:12.297     20170416031522533.jpeg
  1                10        13             rty        1                    2017-06-22 16:13:58.060     20170416031250101.jpeg

This is my excepted o/p =>

 NotificationId | UserId | ActionUserId | UserName | NotificationTypeId | InsertDateTime            | ProfileImage           | NotificationText
   6                20        15             hbc        1                    2017-06-22 17:14:16.803    20170416032403869.jpeg    hbc,tyu and 1  other users followed you
   3                10        11             abc        1                    2017-06-22 16:14:16.803    20170416032403867.jpeg    abc,xyz and 1  other users followed you

Create test data => 

declare @Notifications table(NotificationID int, UserID int, ActionUserID int, NotificationTypeID int, InsertDateTime datetime);
declare @Users table(UserID int, UserName nvarchar(10), ProfileImage nvarchar(50))
insert into @Notifications values (6,20,15,1,'2017-06-22 17:14:16.803'),(5,20,16,1,'2017-06-22 17:12:12.297'),(4,20,17,1,'2017-06-22 17:11:58.060'),(3,10,11,1,'2017-06-22 16:14:16.803'),(2,10,12,1,'2017-06-22 16:14:12.297'),(1,10,13,1,'2017-06-22 16:13:58.060');
insert into @Users values (15,'hbc','20170416032403869.jpeg'),(16,'tyu','20170416031522534.jpeg'),(17,'opl','20170416031250102.jpeg'),(10,'aaa',''),(11,'abc','20170416032403867.jpeg'),(12,'xyz','20170416031522533.jpeg'),(13,'rty','20170416031250101.jpeg');

Upvotes: 2

Views: 653

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5235

Try this:

declare @Notifications table
(NotificationID int, 
UserID int, 
ActionUserID int, 
NotificationTypeID int, 
InsertDateTime datetime);
declare @Users table
(UserID int, 
UserName nvarchar(10), 
ProfileImage nvarchar(50))

insert into @Notifications values 
(6,20,15,1,'2017-06-22 17:14:16.803'),
(5,20,16,1,'2017-06-22 17:12:12.297'),
(4,20,17,1,'2017-06-22 17:11:58.060'),
(3,10,11,1,'2017-06-22 16:14:16.803'),
(2,10,12,1,'2017-06-22 16:14:12.297'),
(1,10,13,1,'2017-06-22 16:13:58.060');
insert into @Users values (15,'hbc','20170416032403869.jpeg'),
(16,'tyu','20170416031522534.jpeg'),
(17,'opl','20170416031250102.jpeg'),
(10,'aaa',''),
(20,'bbb',''),
(11,'abc','20170416032403867.jpeg'),
(12,'xyz','20170416031522533.jpeg'),
(13,'rty','20170416031250101.jpeg');


declare @followCount table
(
userid int,
cnt int
)

INSERT INTO @followCount
select N.UserID, count(*) 
FROM
    @Notifications N
INNER JOIN 
    @Users U ON N.UserId = U.UserId
INNER JOIN 
    @Users AU ON N.ActionUserId = AU.UserId 
GROUP BY n.UserID

declare @msg table
(
userid int,
NotificationMsg varchar(15)
)
INSERT INTO @msg
SELECT DISTINCT N.UserID, Stuff((SELECT ', ' + UserName  
                FROM
(SELECT UserID, UserName FROM (SELECT N.UserId,
    AU.UserName,
    ROW_NUMBER() OVER (PARTITION BY N.UserID ORDER BY N.InsertDateTime DESC) as rn
    FROM
    @Notifications N
INNER JOIN 
    @Users U ON N.UserId = U.UserId
INNER JOIN 
    @Users AU ON N.ActionUserId = AU.UserId
INNER JOIN 
    @followCount C on C.userid = N.UserID) t2 WHERE rn < 3) t2
    WHERE  t2.UserID  = N.UserID  
                FOR XML PATH('')), 1, 2, '') AS NotificationMsg
FROM @Notifications N

; with cte as    
(SELECT N.NotificationId,
        N.UserId,
        N.ActionUserId,
        (CASE WHEN N.NotificationTypeId = 1 THEN 1
            WHEN N.NotificationTypeId = 7 THEN 3
        ELSE    
             2
        END) AS TypeId,                         
        AU.ProfileImage,
        AU.UserName,
        N.NotificationTypeId,
        N.InsertDateTime,
        m.NotificationMsg + CASE WHEN c.cnt > 2 THEN ' + ' 
        + FORMAT(c.cnt - 2, '0') + ' other users' END + ' followed you.' AS NotificationText,
        ROW_NUMBER() OVER (PARTITION BY N.UserID ORDER BY N.InsertDateTime DESC) as rn
FROM 
    @Notifications N
INNER JOIN 
    @Users U ON N.UserId = U.UserId
INNER JOIN 
    @Users AU ON N.ActionUserId = AU.UserId
INNER JOIN 
    @followCount C on C.userid = N.UserID 
INNER JOIN @msg M ON m.userid = N.UserID)

SELECT NotificationID, UserID, ActionUserID, UserName, 
NotificationTypeID, InsertDateTime, ProfileImage, NotificationText 
FROM  cte WHERE rn = 1

Please note I had to add an entry for userid 20. The sample data you gave only produced one record.

What made it particularly tricky, was the requirement two names plus x others. This involved several intermediary steps. It may well be that someone more expert in SQL than I could shorten this. Also please note that I have only tested with this data. You need to check with numbers greater and less than 3 other users to make sure it is all still good.

Upvotes: 2

Related Questions