x
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.