select p.id_p,p.name,p.address,v1.value phone,v2.value ID,v3.value Department from person p,value v1,value v2,value v3 where p.id_p=v1.id_p(+) and v1.type(+)='Phone' and p.id_p=v2.id_p(+) and v2.type(+)='ID' and p.id_p=v3.id_p(+) and v3.type(+)='Department' and exists( select * from value v4 where v4.id_p=p.id_p )select p.id_p,p.name,p.address, (select value from value v1 where v1.id_p=p.id_p and v1.type='Phone') phone, (select value from value v1 where v1.id_p=p.id_p and v1.type='ID') id, (select value from value v1 where v1.id_p=p.id_p and v1.type='Department') department from person p where exists( select * from value v4 where v4.id_p=p.id_p )
select p.ID_P ,max(p.Name) as Name ,max(p.Address) as Address ,max(case when v.Type='Phone' then v.Value else '' end) as Phone ,max(case when v.Type='ID' then v.Value else '' end) as ID ,max(case when v.Type='Department' then v.Value else '' end) as Department from person p left join value v on p.ID_P=v.ID_P group by p.ID_P
from person p,value v1,value v2,value v3
where p.id_p=v1.id_p(+)
and v1.type(+)='Phone'
and p.id_p=v2.id_p(+)
and v2.type(+)='ID'
and p.id_p=v3.id_p(+)
and v3.type(+)='Department'
and exists(
select * from value v4 where v4.id_p=p.id_p
)select p.id_p,p.name,p.address,
(select value from value v1 where v1.id_p=p.id_p and v1.type='Phone') phone,
(select value from value v1 where v1.id_p=p.id_p and v1.type='ID') id,
(select value from value v1 where v1.id_p=p.id_p and v1.type='Department') department
from person p
where exists(
select * from value v4 where v4.id_p=p.id_p
)
,max(p.Name) as Name
,max(p.Address) as Address
,max(case when v.Type='Phone' then v.Value else '' end) as Phone
,max(case when v.Type='ID' then v.Value else '' end) as ID
,max(case when v.Type='Department' then v.Value else '' end) as Department
from person p left join value v on p.ID_P=v.ID_P
group by p.ID_P