select 
    id,
    case flag when 0 then max(property) end title,
    ...
from
    tbl
group by
    id 

 SQL>  with dept as
  2  (
  3      select 1 d_id, 'hr' name from dual union all
  4      select 2, 'fin' from dual
  5  ),
  6  tbl as
  7  (
  8      select 11 u_id, null d_id, 'Petrov' prop, 1 flag from dual union all
  9      select 11, 1, null, 2 from dual union all
 10      select 22, null, 'Ivanov', 1 from dual union all
 11      select 22, null, 'Nachal`nik', 0 from dual union all
 12      select 22, 2, null, 2 from dual union all
 13      select 33, null, 'Sidorov', 1 from dual union all
 14      select 33, null, 'Calculator', 0 from dual union all
 15      select 33, 2, null, 2 from dual
 16  )
 17  select
 18      tbl.prop,
 19      min(dept.name) over (partition by tbl.u_id) d_name
 20  from
 21      tbl,
 22      dept
 23  where
 24      tbl.d_id = dept.d_id(+)
 25  order by
 26     d_name, u_id, flag
 27 /

PROP       D_N
---------- ---
Nachal`nik fin
Ivanov     fin
           fin
Calculator fin
Sidorov    fin
           fin
Petrov     hr
           hr

8 rows selected.

max(case flag when ...)

SQL> with tbl as
  2  (
  3   select 1 id, 0 flag, 'head' property from dual union all
  4   select 1 id, 1 flag, 'Ivan' property from dual union all
  5   select 1 id, 3 flag, '500' property from dual
  6  )
  7  select
  8     *
  9  from
 10     tbl
 11  pivot (max(property) for flag in (0 "TITLE", 1 "NAME", 3 "SOMETHING"));

        ID TITL NAME SOME
---------- ---- ---- ----
         1 head Ivan 500

SQL> ed
Wrote file afiedt.buf

  1  with tbl as
  2  (
  3   select 1 id, 0 flag, 'head' property from dual union all
  4   select 1 id, 1 flag, 'Ivan' property from dual union all
  5   select 1 id, 3 flag, '500' property from dual
  6  )
  7  select
  8     id,
  9     max(case flag when 0 then property end) title,
 10     max(case flag when 1 then property end) name,
 11     max(case flag when 3 then property end) something
 12  from
 13     tbl
 14  group by
 15*     id
SQL> /

        ID TITL NAME SOME
---------- ---- ---- ----
         1 head Ivan 500