SQL：将行转换为列

小编典典

SQL：将行转换为列

sql

考虑下面的示例，其中有一个包含人员记录的 Person 表和一个包含链接到人员的可选属性的 PersonAttribute 表：

Table: Person

ID    Name
1     Joe Bloggs
2     Jane Doe

Table PersonAttribute

PersonId  Key         Value
1         Age         27            
2         HairColor   Brown

我将如何编写一个查询，使所有具有属性的人都像列一样返回？我需要的结果集是：

ID    Name        Age    HairColor
1     Joe Bloggs  27     
2     Jane Doe           Brown

因此，从本质上讲，我需要编写一个查询，该查询将获取所有带有所有唯一属性键的人记录，这些键被转换为具有每个人记录值的列。

请注意， PersonAttribute 表上的主键是 PersonID 和 Key的
组合，因此对于特定的key和person，我们将没有重复的条目。

显然，我可以将 Age 和 HairColor 添加为 Person 表中的字段，而根本不使用
PersonAttribute
表，但这只是说明问题的一个示例。实际上，我有大量的自定义属性，这些属性对于不同的人记录而言千差万别，因此以这种方式进行操作是不切实际的。

阅读 194

2021-04-28

共1个答案

小编典典

我无法谈论MySQL，但是在PostgreSQL中，您可以使用tablefunc模块中的crosstab函数：

CREATE OR REPLACE VIEW PersonAttributePivot AS
    SELECT PersonId AS ID, Age, HairColor
    FROM crosstab
    (
       'SELECT PersonId, Key, Value FROM PersonAttribute',
       'SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key'
    )
    AS
    (
        PersonId integer,
        Age text,
        HairColor text
    );

加盟查询：

SELECT id, name, age, haircolor
FROM Person JOIN PersonAttributePivot USING(id)
ORDER BY id;

想要的结果：

 id |    name    | age | haircolor 
----+------------+-----+-----------
  1 | Joe Bloggs | 27  | 
  2 | Jane Doe   |     | Brown
(2 rows)

如您所见，我在PersonAttributePivot视图中放置了明确的列列表。我不知道隐式列列表的任何“自动透视”创建方式。

编辑：

对于 庞大的 列列表（假设总是text键入）作为一种解决方法，我看到了这种修改很少的方法：

动态类型创建（这里是基于Java的）：

Class.forName("org.postgresql.Driver");
Connection c =
        DriverManager.getConnection("jdbc:postgresql://localhost/postgres", "postgres", "12345");
Statement s = c.createStatement();
ResultSet rs = s.executeQuery("SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key");
List<String> columns = new ArrayList<String>();

while (rs.next())
    columns.add(rs.getString(1));

System.out.println("CREATE TYPE PersonAttributePivotType AS (");
System.out.println("\tPersonId integer,");
for (int i = 0; i < columns.size(); ++i)
{
    System.out.print("\t" + columns.get(i) + " text");
    if (i != columns.size() - 1)
        System.out.print(",");
    System.out.println();
}
System.out.println(");");

结果：

CREATE TYPE PersonAttributePivotType AS (
    PersonId integer,
    Age text,
    HairColor text
);

功能包装器：

CREATE OR REPLACE FUNCTION crosstabPersonAttribute(text, text)
    RETURNS setof PersonAttributePivotType
    AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;

自动创建视图：

CREATE OR REPLACE VIEW PersonAttributePivot AS
    SELECT * FROM crosstabPersonAttribute
    (
       'SELECT PersonId, Key, Value FROM PersonAttribute',
       'SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key'
    );

结果：

TABLE PersonAttributePivot;
 personid | age | haircolor
----------+-----+-----------
        1 | 27  |
        2 |     | Brown
(2 rows)

2021-04-28