When a 'Achieved' row is added for 'ABC' then above methods do not work, we need a solution for any data that working true
select distinct Organization,Year,Target
from
(select * from [manufactor_status] c left join
(select * from
(select [Organization] o ,[Year] y, IIf([Target]='Achieved' , 'Achieved' ,'') [Target2]
from [dbo].[manufactor_status]) a
where a.Target2 !='') b
on c.Organization=b.o) d
where (d.Target=d.Target2 and d.Target='Achieved') or (d.Target='Partial' and d.Target2 is null)
when data in table is change, it is working true