Query to select distinct values from different tables and not have them repeat (show them as a flat file)

Question

I'm trying to get all phones, emails, and organizations for a person and show it in a flat file format. There should be n number of rows, where n is the max count of organizations, emails, or phones. NULL values will be shown once all values have been shown in the rows, with NULL being the last values. The emails and phones can only have 1 PreferredInd per person. I want these to be on the same row (1 of them can be NULL). I've tried to do this on a more complex query, but couldn't get it to work, so I've started over using this simpler example.

Example tables and values:

@ContactPerson
Id        Name
1        John Doe

@ContactEmail
Id   PersonId     Email            PreferredInd
1    1            [email protected]     0
2    1            [email protected]        1
3    1            [email protected]  0

@ContactPhone
Id   PersonId     Phone            PreferredInd
1    1            888-867-5309       0
2    1            305-476-5234       1

@ContactOrganization
Id   PersonId     Organization
1    1            US Government
2    1            US Army

I want a resulting set to look like:

Name           Organization    PreferredInd  Email             Phone
John Doe       US Government   1            [email protected]        888-867-5309
John Doe       US Army         0            [email protected]     305-467-5234
John Doe       NULL            0            [email protected]  NULL

The complete sql code that I have for this example is here on pastebin. It also includes code to create the sample tables. It works when the count of emails exceeds the count of organizations or phones, but that won't always be true. I can't seem to figure out how to get the result that I'm looking for. The actual tables I'm working with can have 0 or infinity emails, phones, or organizations per person. There will also be many more values, but I can fix that myself.

Can you help me fix my query or show me a simpler way to do it? If you have any questions, just let me know and I can try to answer them.

Answer 1

something like this?

with cte_e as (
    select
        *,
        row_number() over(order by PreferredInd desc, Id) as rn
    from ContactEmail
), cte_p as (
    select
        *,
        row_number() over(order by PreferredInd desc, Id) as rn
    from ContactPhone
), cte_o as (
    select
        *,
        row_number() over(order by Organization) as rn
    from ContactOrganization
), cte_d as (
    select distinct rn, PersonId from cte_e union
    select distinct rn, PersonId from cte_p union
    select distinct rn, PersonId from cte_o
)
select
    pr.Name, o.Organization, e.Email, p.Phone
from cte_d as d
    left outer join ContactPerson as pr on pr.Id = d.PersonId
    left outer join cte_e as e on e.PersonId = d.PersonId and e.rn = d.rn
    left outer join cte_p as p on p.PersonId = d.PersonId and p.rn = d.rn
    left outer join cte_o as o on o.PersonId = d.PersonId and o.rn = d.rn

sql fiddle demo

it's a bit clumsy, I can think of couple of other possible ways to do this, but I think this one is most readable one