扁平化 SQL Server 中的父子关系

我在 SQL Server 中有两个表：Household 和 People。 Household 代表一个家，People 代表住在家里的人：

家庭

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

People

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

我想查询这两个表并最终得到如下结果集，但我不确定如何最好地解决这个问题：

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

当然，“PersonXName 和 PersonXAge”应该根据有多少人重复。我怎样才能编写一个查询来完成这个任务？简单性优于性能，因为这是我需要提出的一次性报告。

这是使用动态交叉表完成的。以供参考：http://www.sqlservercentral.com/articles/Crosstab/65048/ 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