Select query to aggregate

Question

I have a table with following data:

Id groupId status
1 100 Completed
2 100 Completed
3 100 Pending
4 200 Completed
5 200 Completed
6 300 Pending
7 300 Pending

Needed following query output:

GroupId status
100 Partially_completed
200 Completed
300 Pending

Logic:

1. If all records for group are in "Pending" status, Output should be pending.
2. If all records for group are in "Completed" status, Output should be completed
3. If any one is completed and any is pending, Output should be partially completed.

Answer 1

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

Answer 2

Hi，@RamiReddy Dd
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.