如何将垂直表格转换为水平表格？

Question

我有一个表 Person：

Id Name
1  Person1
2  Person2
3  Person3

我有它的子表 Profile：

Id PersonId FieldName  Value
1  1        Firstname  Alex
2  1        Lastname   Balmer
3  1        Email      [email protected]
4  1        Phone      +1 2 30004000

我想从这两个表中获取一行中的数据，如下所示：

Id Name     Firstname Lastname  Email                Phone 
1  Person1  Alex      Balmer    [email protected]  +1 2 30004000

1. 在一行中获取这些垂直（键，值）值的最优化查询是什么这？现在我遇到一个问题，我做了四个子表到父表的连接，因为我需要获取这四个字段。一些优化肯定是可能的。
2. 当我添加新字段（键，值）时，我希望能够以简单的方式修改此查询。最好的方法是什么？创建一些存储过程？

我希望在数据库层（C#）中具有强类型并使用 LINQ（编程时），因此这意味着当我在配置文件表中添加一些新的键、值对时，如果可能的话，我希望在数据库和 C# 中进行最少的修改。实际上，我正在尝试在这种情况下获得一些最佳实践。

Answer 1

Select 
    P.ID
    , P.Name
    , Case When C.FieldName = 'FirstName' Then C.Value Else NULL END AS FirstName
    , Case When C.FieldName = 'LastName' Then C.Value Else NULL END AS LastName
    , Case When C.FieldName = 'Email' Then C.Value Else NULL END AS Email
    , Case When C.FieldName = 'Phone' Then C.Value Else NULL END AS Phone  
From Person AS P
Inner JOIN Child AS C
ON P.ID = C.PersonID

您可以使用 PIVOT；不确定哪一个最适合您添加新列。

Answer 2

使用强类型字段的最佳优化方法是这样做：

CREATE TABLE Persons
(PersonID     int identity(1,1) primary key
,Firstname    varchar(...)
,Lastname     varchar(...)
,Email        varchar(...)
,Phone        varchar(...)
,....
)

那么最优化的查询将是：

SELECT
    PersonID,Firstname,Lastname,Email,Phone
    FROM Persons
    WHERE ...

将所有主列添加到人员表中。如果您需要专门创建额外的表：

--one person can play many instruments with this table
CREATE TABLE PersonMusicians
(PersonID               int           --pk   fk to Persons.PersonID     
,InstrumentCode         char(1)       --pk
,...
)

--only one row per person with this table
CREATE TABLE PersonTeachers
(PersonID               int            --pk   fk to Persons.PersonID    
,FavoriteSubjectCode    char(1)
,SchoolName             varchar(...)
)

如果您必须拥有无限的动态属性字段，那么我将尽可能完整地创建上述结构（尽可能多的公共字段），然后有一个“AdditionalInfo”表，您可以在其中存储所有内容 信息

AdditionalInfoFields
FieldID    int identity(1,1) primary key
FieldName  varchar(...)

AdditionalInfo
AdditionalInfoID   int identity(1,1) primary key
PersonID           int   fk to Persons.PersonID  
FieldID            int   fk to AdditionalInfoFields.FieldID    
FieldValue         varchar(..) or you can look into sql_variant

例如：在 AdditionalInfo.PersonID+FieldID 上有一个索引，如果您要搜索具有属性 X 的所有人员，那么还有另一个类似 AdditionalInfo.FieldID+PersonID 的

简短 对于上述任何一个，您将需要使用四个左外连接，就像您在选项 #1 中提到的那样：

SELECT
    P.ID, p.Name
        , p1.Value AS Firstname
        , p2.value AS Lastname     
        , p3.Value AS Email
        , p4.Value AS Phone
    FROM Persons                 p
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Firstname'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Lastname'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Email'
        LEFT OUTER JOIN Profile p1 ON p.PersonID=p1.PersonID AND p1.FieldName='Phone'
    WHERE ....

您始终可以使用这 4 个左连接查询中的索引创建一个物化视图，并为您预先计算数据这应该会加快速度。