CODEWITHSUNDEEP
sql-servert-sql
coderwill
coderwill

Reputation: 844

How to marge data from the table using sql server?

Hi i am create the one store procedure for the get data and send push notification.and i want to marge this data and get in to one raw. but how can do that i don't know. i need help please help me let me know how can do that.this is my query here below i have write :

This is query =>

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 expected 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

i want to like this marge this data any one know how can do that please let me know.

Upvotes: 0

Views: 188

Answers (2)

Sergio Prats
Sergio Prats

Reputation: 1213

You can try this:

SELECT Q.NotificationId,
   Q.UserId,
   Q.ActionUserId,
   (CASE WHEN Q.NotificationTypeId = 1 THEN 1
              WHEN Q.NotificationTypeId = 7 THEN 3
            ELSE    
                 2
        END) AS TypeId,                         
    Q.ProfileImage,
    Q.UserName,
    Q.IsRead,
    (CASE WHEN Q.NotificationTypeId = 1 THEN 1
          WHEN Q.NotificationTypeId = 7 THEN 3
        ELSE    
             2
    END) AS TypeId2,         
    Q.NotificationTypeId,
    Q.InsertDateTime
  FROM ( SELECT    N.UserId, N.ActionUserId, N.NotificationTypeId,
      AU.ProfileImage, AU.UserName, N.IsRead, N.InsertDateTime,         N.NotificationID
    FROM Notifications N
        INNER JOIN Users U ON N.UserId = U.UserId
        INNER JOIN Users AU ON N.ActionUserId = AU.UserId
    WHERE N.UserId = @UserId  )  Q                                
    INNER JOIN (SELECT MAX(NotificationID) AS MaxNotifID, UserID FROM         
dbo.Notifications  
        WHERE UserID = @userID GROUP BY UserID ) R ON
            Q.NotificationID  = R.MaxNotifID AND Q.UserID = R.USerID 
  ORDER BY Q.InsertDateTime DESC

Upvotes: 0

iamdave
iamdave

Reputation: 12243

You can do this with a derived table and some windowed functions. I have also added in a bit of logic to make sure the Notification Text has the correct English depending on the number of other users included:

-- 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');


-- Specify UserID
declare @UserID int = 10;

-- Create Notification
with d as
(
    select n.NotificationID
          ,n.UserID
          ,n.ActionUserID
          ,au.UserName
          ,n.NotificationTypeID
          ,n.InsertDateTime
          ,au.ProfileImage
          ,row_number() over (partition by n.UserID order by n.InsertDateTime desc) as rn
          ,count(*) over (partition by n.UserID) as c
    from @Notifications n
        join @Users au
            on(n.ActionUserID = au.UserID)
)
select d.NotificationID
        ,d.UserID
        ,d.ActionUserID
        ,d.UserName
        ,d.NotificationTypeID
        ,d.InsertDateTime
        ,d.ProfileImage
        ,d.UserName
         + isnull(case when d2.c = 2
                       then ' and '
                       else ', '
                       end
                  + d2.UserName
                  ,'')
         + case when d2.c > 2
                then ' and ' + cast(d2.c-2 as nvarchar(10)) + ' other users'
                else ''
                end
         + ' followed you' as NotificationText
from d
    left join d as d2
        on(d.UserID = d2.UserID
            and d2.rn = 2
            )
where d.rn = 1;

Output:

+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+
| NotificationID | UserID | ActionUserID | UserName | NotificationTypeID |     InsertDateTime      |      ProfileImage      |            NotificationText             |
+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+
|              3 |     10 |           11 | abc      |                  1 | 2017-06-22 16:14:16.803 | 20170416032403867.jpeg | abc, xyz and 1 other users followed you |
|              6 |     20 |           15 | hbc      |                  1 | 2017-06-22 17:14:16.803 | 20170416032403869.jpeg | hbc, tyu and 1 other users followed you |
+----------------+--------+--------------+----------+--------------------+-------------------------+------------------------+-----------------------------------------+

Upvotes: 1

Related Questions