Vitrum Vitrum - 1 month ago 4
SQL Question

query data with same id but different values oracle

I have two tables like these

Person_table
| person_id | name | surname
--------------------------------
1 | name1 | surname1
--------------------------------
2 | name2 | surname2
--------------------------------
3 | name3 | surname3


insurance_table

ins_id |person_id | insurance_type |
-------------------------------------
001 | 1 | 5% |
-------------------------------------
002 | 1 | 10% |
-------------------------------------
003 | 3 | 2% |


after joining as following

select pt.*, it.insurance_type
from person_table pt
join insurance_table it on it.person_id=pt.person_id


the query returns duplicating rows for person_id = 1.
like this

| person_id | person_name | insurance_type |
----------------------------------------------
| 1 | name1 | 5% |
-----------------------------------------------
| 1 | name1 | 10% |


the point is to return a single row for every id with additional columns according to insurance type,like this

| person_id | person_name | type 1 | type2 | type3 |
----------------------------------------------------------------------------
1 | name1 | 5% | 10% | null |
----------------------------------------------------------------------------
2 | name2 | null | null | 2% |


is it possible to implement such a query without writing pl procedure.
Thanks a lot!

Answer

If i understand, you want to get persons with different types of insurance. If we have just 3 types its look like you need simple decode

select person_id, person_name, max(type1) as type1,  max(type2) as type2, max(type3) as  type3 from ( 
select pt.*, decode( it.insurance_type,'5%',it.insurance_type) as type1 
       , decode( it.insurance_type,'10%',it.insurance_type) as type2
       , decode( it.insurance_type,'2%',it.insurance_type) as type3
from person_table pt 
join insurance_table it on it.person_id=pt.person_id)
group by  person_id, person_name
Comments