Skip to content
Advertisement

Group by the records and that particular group if condition matched then set final result like below

declare @table table
(
    id int,
    status varchar(50)
)

insert into @table
    values(1,'Open'),(1,'Open'),(1,'Open'),
    (2,'Open'),(2,'Pending'),(2,'Open'),
    (3,'Open'),(3,'Completed'),(3,'Pending'),
    (4,'Pending'),(4,'Pending'),(4,'Pending'),
    (5,'Completed'),(5,'Completed'),(5,'Completed')

I want to group by the records and that particular group if the condition matched then set final result like below

--expected output
--Id    Status
--1     Open
--2     Pending
--3     Pending
--4     Pending
--5     Completed

For each group, I want to check the following condition

--Open
--Open => Open
--Open

--Open
--Pending => Pending
--Open

--Open
--Completed => Pending
--Open

--Pending
--Pending =>Pending
--Pending

--Completed
--Completed =>Completed
--Completed

Advertisement

Answer

You can use conditional logic:

select id,
       (case when max(status) = min(status) then max(status)
             else 'Pending'
        end) as status 
from @table t
group by id;

Here is a db<>fiddle.

3 People found this is helpful
Advertisement