sql to transpose rows to columns

Question

I have an Oracle view (joined from multiple tables) like the first screen shot. the address_type should be always either owner or property. I want to get results like the second screen shot. How to use SQL to do that? Sorry I am a very beginner to SQL.

Possible duplicate of [SQL Server Pivot Table with multiple column aggregates](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) — Tab Alleman, Jul 13 '16 at 14:00
Not really a duplicate, since they didn't know of the `pivot` statement, but yeah, that one's a good start. — Philip Kelley, Jul 13 '16 at 14:01
Possible duplicate of [Oracle SQL pivot query](http://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — sstan, Jul 13 '16 at 14:24

score 1 · Accepted Answer · answered Jul 13 '16 at 14:36

You can use an inner join:

select tab1.address_id, tab1.address as owner_address,
       tab1.city as owner_city, tab1.state as owner_state, 
       tab1.zip as owner_zip, tab2.address as property_address,
       tab2.city as property_city, tab2.state as property_state, 
       tab2.zip as property_zip  
from tab1
full outer join tab2
on tab1.address_id = tab2.address_id 
where tab1.address_type = 'owner'
      and tab2.address_type = 'property'

tab1 contains all owner informations, tab2 contains all property informations. You can join them using address_id.

Sorry i can't test it!

thank you. just some minor changes. I was able to make it work with your codes. — Alex W., Jul 13 '16 at 15:20

sql to transpose rows to columns

1 Answers1