Flatten parent child relationship in SQL Server

Question

I have two tables in SQL Server: Household and People. Household represents a home and People represents the people living in the home:

Household

Id       Address        City        State          Zip
------------------------------------------------------
1        123 Main       Anytown     CA           90121

People

Id        HouseholdId       Name        Age
-------------------------------------------
1         1                 John         32
2         1                 Jane         29

I want to query the two tables and end up with a result set like below, but I'm not sure how best to approach this:

Id        Address        City        State        Zip        Person1Name        Person1Age        Person2Name     Person2Age
----------------------------------------------------------------------------------------------------------------------------
1         123 Main       Anytown     CA           90121      John                       32        Jane                    29

Of course, "PersonXName and PersonXAge" should repeat based on how many people there are. How can I write a query that would accomplish this? Simplicity is preferred over performance as this is a one-off report I need to come up with.

Answer 1

This is done using a dynamic cross tab. For reference: http://www.sqlservercentral.com/articles/Crosstab/65048/

CREATE TABLE HouseHold(
    ID      INT,
    Address VARCHAR(20),
    City    VARCHAR(20),
    State   CHAR(2),
    Zip     VARCHAR(10)
)
CREATE TABLE People(
    ID          INT,
    HouseHoldID INT,
    Name        VARCHAR(20),
    Age         INT
)
INSERT INTO HouseHold VALUES
(1, '123 Main', 'Anytown', 'CA', '90121');
INSERT INTO People VALUES
(1, 1, 'John', 32),
(2, 1, 'Jane', 29);

DECLARE @sql1 VARCHAR(4000) = ''
DECLARE @sql2 VARCHAR(4000) = ''
DECLARE @sql3 VARCHAR(4000) = ''

SELECT @sql1 =
'SELECT
     ID
    ,Address
    ,City
    ,State
    ,Zip'
+ CHAR(10)

SELECT @sql2 = @sql2 +
'   ,MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(10), RN) + ' THEN Name END) AS [Person' + CONVERT(VARCHAR(10), RN) + 'Name]
    ,MAX(CASE WHEN RN = ' + CONVERT(VARCHAR(10), RN) + ' THEN Age END) AS [Person' + CONVERT(VARCHAR(10), RN) + 'Age]
'
FROM(
    SELECT DISTINCT RN = ROW_NUMBER() OVER(PARTITION BY p.HouseHoldID ORDER BY p.ID)
    FROM People p   
)t

SELECT @sql3 =
'FROM(
    SELECT
        h.*
        ,p.Name
        ,p.Age
        ,RN = ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY p.ID)
    FROM Household h
    INNER JOIN People p ON p.HouseHoldId = h.ID
)t
GROUP BY ID, Address, City, State, Zip
ORDER BY ID'

PRINT(@sql1 + @sql2 + @sql3)
EXEC (@sql1 + @sql2 + @sql3)

DROP TABLE HouseHold
DROP TABLE People

RESULT

ID          Address              City                 State Zip        Person1Name          Person1Age  Person2Name          Person2Age
----------- -------------------- -------------------- ----- ---------- -------------------- ----------- -------------------- -----------
1           123 Main             Anytown              CA    90121      John                 32          Jane                 29