Q:

How to merge multiple rows from the same table into a single row result?

I am trying to figure out how to merge multiple addresses into a single row w/o duplicating any records.
Company Table
|---------------------|------------------|
|      CompanyId      |     Name         |
|---------------------|------------------|
|          1          |     ABC          |
|---------------------|------------------|
|          2          |     XYZ          |
|---------------------|------------------|

AddressType Table
|---------------------|------------------|
|  AddressTypeId      |   AddressType    |
|---------------------|------------------|
|          1          |     Location     |
|---------------------|------------------|
|          2          |     Shipping     |
|---------------------|------------------|
|          3          |     Billing      |
|---------------------|------------------|

CompanyAddress Table
|---------------------|------------------|------------------|
|  CompanyID          |   AddressId      |   AddressTypeId  |
|---------------------|------------------|------------------|
|          1          |     1            |     1            |
|---------------------|------------------|------------------|
|          1          |     2            |     2            |
|---------------------|------------------|------------------|
|          1          |     2            |     3            |
|---------------------|------------------|------------------|

Address Table
|---------------------|------------------|
|     AddressId       |      Address     |
|---------------------|------------------|
|          1          |     123 Main St  |
|---------------------|------------------|
|          2          |     156 Front St |
|---------------------|------------------|

The result I want is this:
|-------------|--------|-------------------|-------------------|------------------|
|  CompanyId  |  Name  |  LocationAddress  |  ShippingAddress  |  BillingAddress  |
|-------------|--------|-------------------|-------------------|------------------|
|  1          |  ABC   | 123 Main St       |  156 Front St     |  156 Front St    |
|-------------|--------|-------------------|-------------------|------------------|

I thought originally I could do multiple joins on the CompanyAddress and Address table like this:
SELECT
     c.CompanyId
     , c.CompanyName
     , la.Address as LocationAddress  
     , sa.Address as ShippingAddress  
     , ba.Address as BillingAddress  
FROM
     Company c
     JOIN CompanyAddress al ON ca.CompanyID = c.CompanyID an ca.AddressTypeId = 1
     JOIN CompanyAddress as ON ca.CompanyID = c.CompanyID an ca.AddressTypeId = 2
     JOIN CompanyAddress ab ON ca.CompanyID = c.CompanyID an ca.AddressTypeId = 3
     JOIN Address la ON la.AddressId = al.AddressId
     JOIN Address sa ON sa.AddressId = as.AddressId
     JOIN Address ba ON sa.AddressId = ab.AddressId

But that gets me this:
|-------------|--------|-------------------|-------------------|------------------|
|  CompanyId  |  Name  |  LocationAddress  |  ShippingAddress  |  BillingAddress  |
|-------------|--------|-------------------|-------------------|------------------|
|  1          |  ABC   | 123 Main St       |  null             |  null            |
|-------------|--------|-------------------|-------------------|------------------|
|  1          |  ABC   | null              |  156 Front St     |  null            |
|-------------|--------|-------------------|-------------------|------------------|
|  1          |  ABC   | null              |  null             |  156 Front St    |
|-------------|--------|-------------------|-------------------|------------------|

I understand why I am getting this result. But I am not seeing the correct query in my head.

A:

You can do conditional aggregation:
select 
    ca.CompanyId,
    ca.CompanyName,
    max(case when at.AddressType = 'Location' then a.Address end) LocationAddress,
    max(case when at.AddressType = 'Shipping' then a.Address end) ShippingAddress,
    max(case when at.AddressType = 'Billing'  then a.Address end) BillingAddress
from Company c
inner join CompanyAddress ca on ca.CompanyId = c.CompanyId
inner join Address a on a.AddressId = ca.AddressId
inner join AddressType at on at.AddressTypeId = ca.AddressTypeId
group by ca.CompanyId , ca.CompanyName

