question

 DECLARE @tbl TABLE (id int, groupid int, status varchar(100))
    
 INSERT INTO @tbl 
 SELECT *
 FROM (VALUES
 (1,100,'Completed'),
 (2,100,'Completed'),
 (3,100,'Pending'),
 (4,200,'Completed'),
 (5,200,'Completed'),
 (6,300,'Pending'),
 (7,300,'Pending')
 ) a (id, groupid, status)
    
 ;with cte as (
     SELECT 
         groupid,
         MAX(CASE WHEN [status] = 'Completed' THEN 1 ELSE 0 END) AS IsComplete,
         MAX(CASE WHEN [status] = 'Pending' THEN 1 ELSE 0 END) AS IsPending
     FROM @tbl
     GROUP BY groupid
 )
 SELECT groupid,
     CASE 
         WHEN IsComplete=1 AND IsPending=1 THEN 'Partially Completed'
         WHEN IsComplete=1 THEN 'Completed'
         WHEN IsPending=1 THEN 'Pending'
     END as [Status]
 FROM cte
 ORDER BY groupid

Hi，@RamiReddyDd-3575
Please check this：

 ;with cte as
 (
  (select * from (select id, groupid,status from tablename) as t
            pivot(max(id) for t.status in ([Completed],[Pending]) ) as p)
 )
 select groupid, 
         case when COALESCE (Completed ,0)=0 then 'Pending'
              when COALESCE (Pending ,0)=0 then 'Completed'
              else 'Partially Completed'
              end as [status]
  from cte

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.