Exporting normalized relational data from database to flat file format

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

Contact table

We need to create a flat file export from this where the output needs to look like:

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.