在SQL Server中展开父子关系

Question

我是一个有用的助手，可以翻译文本。

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

Household

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

人们。

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” 应根据人数重复。我该如何编写一个查询来实现这一点？在这里，简单性优先于性能，因为这是我需要提供的一次性报告。

- Scott

---

数据透视表或交叉报表。为什么要存储年龄？你应该存储出生日期。 - Sean Lange

这只是一个任意的例子。真正的模式不同，也没有“年龄”列。我更多地是在寻找基于简单数据的样本查询，我会根据我的实际模式进行调整。 - Scott

没错 @SeanLange +1。年龄可以从出生日期推算出来。 - Rahul

@user2989408 这不是真的。你可以使用动态透视表。我更喜欢交叉表方法。从性能上来说，它们更快，并且更容易理解。这是一种静态方法。http://www.sqlservercentral.com/articles/T-SQL/63681/ 这是动态版本http://www.sqlservercentral.com/articles/Crosstab/65048/ - Sean Lange

@SeanLange 根据我之前的评论，我的意思是无法使用静态SQL完成此操作。当然，你可以通过构建动态SQL来实现。 - user2989408

Answer 1

使用动态交叉表完成此操作。参考链接： 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

结果

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

- Felix Pamittan

Answer 2

这是我在类似需求时使用的脚本改编而来。如果People表有一百万行，则可能不适用，但对于我的使用情况（约20000行），效果足够好。

DECLARE @id int, @householdid int, @name varchar(50), @age int, @currentid   int, @peoplecount int;
DECLARE @colsql nvarchar(1000), @datasql nvarchar(1000), @RunSql nvarchar(1000);

CREATE TABLE #ReturnTable (HouseholdId int, Address varchar(50))

INSERT #ReturnTable
SELECT Id, Address
FROM Household;

-- these are split into two dynamic queries
-- so that columns exist when we try the insert
SET @colsql = 'IF (SELECT COUNT(*)
  FROM TempDB.INFORMATION_SCHEMA.COLUMNS 
  WHERE COLUMN_NAME = ''Person{Number}Name'' 
    AND TABLE_NAME LIKE ''#ReturnTable'') = 0
BEGIN
  ALTER TABLE #ReturnTable
  ADD Person{Number}Name VARCHAR(50)
END

IF (SELECT COUNT(*)
  FROM TempDB.INFORMATION_SCHEMA.COLUMNS 
  WHERE COLUMN_NAME = ''Person{Number}Age'' 
    AND TABLE_NAME LIKE ''#ReturnTable'') = 0
BEGIN
  ALTER TABLE #ReturnTable
  ADD Person{Number}Age INT
END'

set @datasql =
'UPDATE #ReturnTable
SET Person{Number}Name = @name,
  Person{Number}Age = @age
WHERE HouseholdId = @householdid'

DECLARE PeopleCursor CURSOR FOR
SELECT p.Id, p.HouseholdId, p.Name, p.Age
FROM People p
ORDER BY p.HouseholdId, p.Age

OPEN PeopleCursor;

FETCH NEXT FROM PeopleCursor
INTO @id, @householdid, @name, @age

SET @currentid = @id

SET @peoplecount = 1;

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @currentid <> @id
    BEGIN
       SET @peoplecount = 1
       SET @currentid = @id
    END
    ELSE SET @peoplecount = @peoplecount + 1;

    SET @RunSql = REPLACE(@colsql, '{Number}', CAST(@peoplecount AS VARCHAR(3)));

    EXEC dbo.sp_ExecuteSql @RunSql

    SET @RunSql = REPLACE(@datasql, '{Number}', CAST(@peoplecount AS VARCHAR(3)));

    EXEC dbo.sp_ExecuteSql @RunSql, N'@householdid int, @name varchar(50), @age int', @householdid = @householdid, @name = @name, @age = @age;

    FETCH NEXT FROM PeopleCursor
    INTO @id, @householdid, @name, @age
END

CLOSE PeopleCursor
DEALLOCATE PeopleCursor

SELECT *
FROM #ReturnTable

drop table #ReturnTable

- Matthew Jaspers

Answer 3

这个"展平(flattening)"操作被称为多列动态透视表。它是动态的，因为在设计时不知道透视表的列值，且它是多列的，因为你有"年龄(age)"和"名字(name)"透视表的列值。

要在SQL中进行多列动态透视表，您需要使用动态SQL并求助于条件表达式。SQL Pivot运算符无法处理多列透视。这很复杂实现。

我认为SQL不是执行多列动态透视最好的语言。我认为最好在客户端完成。

以下C#方法返回一个包含您所请求的结果集的数据表：

public DataTable GetPivotedPeople()
{
    using (var ds = new MyDataService())
    {
        return ds.PersonRepository
            .Query("Household")
            .OrderBy(PersonFields.HouseHoldId, PersonFields.Address, PersonFields.City, PersonFields.State, PersonFields.Zip)
            .Pivot(
                new PivotTransform
                {
                    PivotColumnName = PersonFields.PersonId,
                    ValueColumnName = PersonFields.Name,
                    GetPivotedColumnName = (personId) => "Person" + personId.ToString() + "Name"
                },
                new PivotTransform
                {
                    PivotColumnName = PersonFields.PersonId,
                    ValueColumnName = PersonFields.Age,
                    GetPivotedColumnName = (personId) => "Person" + personId.ToString() + "Age"
                }
            );
    }
}

这是返回的数据表的内容：

+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+
| HouseHoldId | Address  |   Ciy   | State |  Zip  | Person1Name | Person2Name | Person1Age | Person2Age |
+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+
|           1 | 123 Main | Anytown | CA    | 90121 | John        | Jane        |         32 |         29 |
+-------------+----------+---------+-------+-------+-------------+-------------+------------+------------+

它使用我开发的微型ORM EntityLite。它查询Person_HouseHold视图并在客户端旋转行。这里是SQL脚本：

CREATE DATABASE DynamicPivot
GO
USE DynamicPivot
GO
CREATE TABLE Households
(
    HouseholdId int IDENTITY(1,1) PRIMARY KEY,
    [Address] nvarchar(128) NOT NULL,
    City nvarchar(128) NOT NULL,
    [State] nvarchar(128) NOT NULL,
    Zip nvarchar(128) NOT NULL
);
INSERT INTO Households ([Address], City, [State], Zip) 
VALUES (N'123 Main', N'Anytown', N'CA', N'90121');
GO

CREATE TABLE People
(
    PersonId int IDENTITY(1,1) PRIMARY KEY,
    HouseHoldId int NOT NULL CONSTRAINT FK_People_Households REFERENCES HouseHolds(HouseholdId),
    Name nvarchar(128) NOT NULL,
    Age int NOT NULL

);
INSERT INTO People(HouseHoldId, Name, Age) VALUES
(1, N'John', 32), (1, 'Jane', 29)

GO

CREATE VIEW Person_Household
AS
    SELECT 
        P.PersonId, P.HouseHoldId, P.Name, P.Age,
        H.[Address], H.City, H.[State], H.Zip
    FROM
        dbo.People P INNER JOIN dbo.Households H
            ON P.HouseHoldId = H.HouseholdId

我在CodeProject上写了这篇文章。它向你解释了如何使用SQL进行数据透视以及如何使用EntityLite或原始的ADO.NET在客户端执行数据透视。因此，在客户端执行数据透视时，您不需要使用EntityLite。

- Jesús López

Answer 4

只需要做出一些改变，如果你们能帮我解决这个问题，那就太好了...

 DECLARE @col1 nvarchar(max) = '', @col2 nvarchar(max) = ''
 declare @colname nvarchar(max),@query nvarchar(max), @cols nvarchar(max)=''

 DECLARE openall CURSOR for
 SELECT ROW_NUMBER() OVER(ORDER BY NAME) rowno FROM People 

    OPEN openall
  fetch next from openall into @colname

while @@FETCH_STATUS = 0
    begin

        set @col1 += 'Person'+ @colname +'Name,'
        set @col2 += 'Person'+ @colname+'Age,'

        fetch next from openall into @colname
     end     

  set @col1 = LEFT(@col1,LEN(@col1)-1)
  set @col2 = LEFT(@col2,LEN(@col2)-1)

 set @query = 'SELECT ID, Address, City, State, Zip, ' + @col1 + ', ' + @col2 + ' 
       FROM ( 
        SELECT h.ID, Address, City, State, Zip,p.name,p.age from Household h 
         inner join people p on h.id = p.householdid
           ) x
        pivot
       (
      sum(age) for
      name in (' + @col1 + ', ' + @col2 + ')
      ) p '

     execute(@query)
     close openall
     deallocate openall

- pyborg

Answer 5

考虑到这是一次性的，您可以考虑以下方法：

获取同一住所中居住人数最多的数量（选择计数）

为每个人将人员表连接到家庭表。

SELECT * 
FROM Household 
LEFT JOIN People p1 
ON p1.HouseHoldId = Household.Id
LEFT JOIN People p2 
ON p2.HouseHoldId = Household.Id

根据索引、优化设置和许多其他条件，这可能是一个非常高效的解决方案。

- Tristan

---

你的查询会被展平成四行，这四行是两个人员行的所有组合，并不符合问题的目标。你的查询结果如下：http://www.sqlfiddle.com/#!6/81cf7 - Ryan Kyle