Q:

Select a two table join in one result row

I have two tables
Table A with three columns: AID, first name, surname
Tabel B with four columns: BID, AID, Address, TypeOFAddress
Each person in table A has four rows each in table B.
Table_A
_AID_|_FIRST_|_LAST_
  1  | Leo   | White
  2  | Joe   | Black

Table_B
_BID_|_AID_|_ADDRESS_|_TYPE_
  10 |  1  |Street 1 |STREET
  11 |  1  |23490878 |ZIPCODE
  12 |  1  |Town 1   |CITY
  13 |  1  |Street 2 |STREET
  14 |  1  |83083099 |ZIPCODE
  15 |  1  |Town 2   |CITY

I want to select these rows in one row so the result output is
Result output
1 |Leo   |White |Street 1 |23490878 |Town
2  |Joe   |Black |Street 2 |83083099 |Town 2

How do I do this in a nice way?

A:

First of all, JOIN the tables. Then use case expressions to do conditional aggregation:
select t1.AID, t1.FIRST, t1.LAST,
       max(case when t2.TypeOFAddress = 'STREET' then t2.Address end) STREET,
       max(case when t2.TypeOFAddress = 'ZIPCODE' then t2.Address end) ZIPCODE,
       max(case when t2.TypeOFAddress = 'CITY' then t2.Address end) CITY
from table1 t1
left join table2 t2 on t1.AID = t2.AID_
group by t1.AID, t1.FIRST, t1.LAST

