Sometimes you need to export relational normalized data into flat files where a single row comes from various tables. For example, say you want to export all customer records along with their work and home address, and primary phone number in a single row. But the address and contact information are coming from different tables and there can be multiple rows in those table for a single customer. Sometimes there can be no row available in address/phone table for a customer. In such a case, neither INNER JOIN, nor LEFT JOIN/OUTER JOIN will work. How do you do it?
Solution is to use OUTER APPLY.
Consider some tables like this:
Customer Table
| CustomerID | FirstName | LastName | DOB |
| 1 | Scott | Guthrie | 1/1/1950 |
| 2 | Omar | AL Zabir | 1/1/1982 |
Contact table
| CustomerID | ContactType | ContactValue | IsPrimary |
| 1 | WorkAddress | Microsoft | TRUE |
| 1 | HomeAddress | Seattle | FALSE |
| 1 | Phone | 345345345 | FALSE |
| 1 | Phone | 123123123 | TRUE |
| 2 | WorkAddress | London | TRUE |
| 2 | Phone | 1312123123 | FALSE |
We need to create a flat file export from this where the output needs to look like:
| CustomerID | FirstName | LastName | DOB | HomeAddress | WorkAddress | PrimaryPhone | IsPhonePrimary |
| 1 | Scott | Guthrie | 1/1/1950 | Seattle | Microsoft | 123123123 | Yes |
| 2 | Omar | AL Zabir | 1/1/1982 | No Home Address | London | 1312123123 | No |
There are some complex requirement in the output:
- If customer has multiple phone, then it needs to select the one which is flagged as primary.
- If customer has no home address, then it needs to show “No home address” instead of NULL.
- It needs to tell if the phone address we got is primary phone or not.
The query to generate this will be:
SELECT
c.CustomerID,
c.FirstName,
c.LastName,
c.DOB,
'HomeAddress' =
CASE
WHEN home.ContactValue IS NULL THEN 'No Home Address'
ELSE home.ContactValue
END,
work.ContactValue,
phone.ContactValue as PrimaryPhone,
'IsPhonePrimary' =
CASE
WHEN phone.IsPrimary = 1 THEN 'Yes'
ELSE 'No'
END
FROM Customer c
OUTER APPLY (
SELECT TOP 1 ContactValue from Contact WHERE CustomerID = c.CustomerID
AND ContactType = 'HomeAddress'
ORDER BY IsPrimary DESC
) AS home
OUTER APPLY (
SELECT TOP 1 ContactValue from Contact WHERE CustomerID = c.CustomerID
AND ContactType = 'WorkAddress'
ORDER BY IsPrimary DESC
) AS work
OUTER APPLY (
SELECT TOP 1 ContactValue, IsPrimary from Contact WHERE CustomerID = c.CustomerID
AND ContactType = 'Phone'
ORDER BY IsPrimary DESC
) AS phone
All the tricks are in the OUTER APPLY blocks. OUTER APPLY selects the row that needs to appear as the value of the columns in the output after the customer table fields.
The primary address is selected by reverse ordering the rows selected from Contact table by IsPrimary field. Thus the rows having True comes first.
