Flatten a related table into a multiple columns with SQL

I’m trying to exact data from two tables in an IFS database (Oracle backend) using SQL. One table contains a list of Addresses, the other contains the Types those addresses have.

Example:

Address table:

Address1
Address2

AddressType table

Address1, DELIVERY, TRUE
Address1, DOCUMENT, FALSE
Address1, PAY, FALSE
Address2, DELIVERY, FALSE
Address2, PAY, TRUE

I can write a query that successfully gets the addresses, and their types, but I end up with a new row for every address type (essentially it just looks like the AddressType table).

SELECT 
    a.address_ID,
    at.address_type,
    at.address_type_default_b
FROM
    addresses a,
    address_type at
WHERE
    a.address_ID = at.address_ID

What I want is a query that will ‘flatten’ the table, move each type into a column. Based on the above example, the resulting table would have 2 rows, with 4 columns, see below.

Desired result

Address1, TRUE, FALSE, FALSE
Address2, FALSE,*NULL*,TRUE

Note that I don’t care what goes in for Null, blank, NA or NULL

Any help would be much appreciated. I realize I could do this in excel, but with the size of the data and the number of times I’ll have to do this, it would be nice to do this in SQL.

Sorry about the poor formatting of the question… I can’t figure out how to insert tables or get my sql to look correct.

Answer

Assuming you have only 3 statuses for an address and each status can happen only once:

In case you won’t have any value, it will return null as desired

select a.address_ID,max( case when address_type='Delivery' then address_type_default_b end  ) 
,max( case when address_type='Document' then address_type_default_b end  )
,max( case when address_type='Pay' then address_type_default_b end  )
from address_table a
left join addresstype_table at on a.address_ID=b.address_ID
group by a.address_ID

Advertisement

Answer