with attrs_agg as ( 
        select service_id
             , max(case type_id when 1 then value end) attr1
             , max(case type_id when 2 then value end) attr2
          from attr
         where service_id > 0 and type_id in(1,2)
         group by service_id
)
, ids as (select service_id 
            from ...
           group by service_id
           having ... -- отсечка по агрегатам. 
               -- К примеру, только имеющие более одной записи service_id
                  count(*) > 1
          )
select *
  from ids ids, attrs_agg a
 where ids.service_id = a.service_id(+)
;


  select service_id
       , max(case type_id when 1 then value end) attr1
       , max(case type_id when 2 then value end) attr2
    from attr a
   where exists(select null from ... where service_id = a.service_id)
   group by service_id
;