Как вариант:
with
t as
(
select 1 as "Заказ", 10 as "Заявка", 'откр' as "Статус заявки" from dual union all
select 1 as "Заказ", 11 as "Заявка", 'закр' as "Статус заявки" from dual union all
select 1 as "Заказ", 12 as "Заявка", 'закр' as "Статус заявки" from dual union all
select 2 as "Заказ", 20 as "Заявка", 'закр' as "Статус заявки" from dual union all
select 2 as "Заказ", 21 as "Заявка", 'закр' as "Статус заявки" from dual union all
select 3 as "Заказ", 30 as "Заявка", 'закр' as "Статус заявки" from dual union all
select 3 as "Заказ", 31 as "Заявка", 'откр' as "Статус заявки" from dual
)
select "Заказ", "Заявка", "Статус заявки",
nvl2(nullif(count(decode("Статус заявки",'откр',1))
over(partition by "Заказ"),0),'откр','закр') as "Статус заказа"
from t;