Cannot consolidate rows when SELECTING from 4 tables

Take a look at this and see if this is what you're looking for...

      SELECT * INTO #Person FROM (VALUES (1, 'Joe Blow')) p (PersonID, PersonName)

        SELECT * INTO #Address FROM (VALUES (1, '123 Park Place'),(1,'321 Boardwalk St.')) a (PersonID, [Address])

        SELECT * INTO #Email FROM (VALUES (1, 'Joe_Blow@gmail.com'),(1,'jb@hotmail.com'),(1,'jblow@yahoo.com')) e (PersonID, EmailAddress)

        SELECT * INTO #Phone FROM (VALUES (1, '555-321-1234'),(1,'555-333-9874')) p (PersonID, Phone)

        ;WITH n (n) AS (
                SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)
        ), Tally (n) AS (
                SELECT 
                        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
                FROM n
        ), a AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM #Address a
        ), e AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM #Email e
        ), ph AS (
                SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM #Phone ph
        )

        SELECT 
                *
        FROM 
                #Person p
                CROSS JOIN Tally t
                LEFT JOIN a
                        ON p.PersonID = a.PersonID
                        AND t.n = a.rn
                LEFT JOIN e
                        ON p.PersonID = e.PersonID
                        AND t.n = e.rn
                LEFT JOIN ph
                        ON p.PersonID = ph.PersonID
                        AND t.n = ph.rn
        WHERE 
                CONCAT(a.PersonID, e.PersonID, ph.PersonID) <> '' 

---

Jason Long