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

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.

One Comment

  1. Hi Omar,

    Did you know you can write this piece of code:
    CASE WHEN home.ContactValue IS NULL
    THEN ‘No Home Address’
    ELSE home.ContactValue
    END

    much shorter, like this:
    ISNULL(home.ContactValue, ‘No Home Address’)

    or with multiple fields, like this:
    COALESCE(home.ContactValue, work.ContactValue, ‘No Address’)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>