SQL：将行转换为列 [英] Sql: Transposing rows into columns
问题描述
考虑以下示例，其中有一个包含人记录的 Person 表和一个包含与人相关的可选属性的 PersonAttribute 表：
表：人员
ID名称
1 Joe Bloggs
2 Jane Doe
表PersonAttribute
PersonId键值
1年龄27
2 HairColor棕色
我编写一个查询，使所有具有属性的人都像列一样返回？我需要的结果集是：
ID名称年龄HairColor
1 Joe Bloggs 27
2 Jane Doe Brown
因此，基本上我需要编写一个查询，该查询将所有具有所有唯一属性键的人记录转换为列，并包含每条人记录的值。 / p>
请注意， PersonAttribute 表上的主键是 PersonID 和 Key 我们不会重复输入特定的键和人员。
很明显，我可以添加 Age 和 HairColor 作为 Person 表中的字段，根本不使用 PersonAttribute 表，但这仅是说明问题的一个示例。实际上，我有大量的自定义属性，这些属性对于不同的人记录而言千差万别，因此这样做是不现实的。
我无法谈论MySQL，但是在PostgreSQL中，您可以使用 tablefunc 模块：
创建或替换视图PersonAttributePivot AS
SELECT PersonId AS ID，年龄，发色
从交叉表
（
'选择PersonId，键，来自PersonAttribute的值'，
'从个人属性ORDER BY BY键中选择不同的键'
）
AS
（
PersonId整数，
年龄文本，
HairColor文本
）；
加入查询：
选择id，名称，年龄，发色
FROM Person JOIN PersonAttributePivot USING（id）
ORDER BY id;
想要的结果：
id |名称|年龄| haircolor
---- + ------------ + ----- + -----------
1 |乔·博格斯| 27 |
2 |简·杜|棕色
（2行）
如您所见，我在<$中放置了明确的列列表c $ c> PersonAttributePivot 视图。我不知道使用隐式列列表的任何自动旋转创建方式。
编辑：
对于巨大列列表（假定总是 text 类型）作为一种变通办法，我看到了这么小的修改方法：
动态类型创建（此处基于Java）：
Class.forName（ org.postgresql.Driver）；
连接c =
DriverManager.getConnection（ jdbc：postgresql：// localhost / postgres， postgres， 12345）;
语句s = c.createStatement（）;
ResultSet rs = s.executeQuery（从PersonAttribute ORDER BY键中选择DISTINCT键）;
List< String>列=新的ArrayList< String>（）;
而（rs.next（））
columns.add（rs.getString（1））;
System.out.println（创建类型PersonAttributePivotType AS（）;
System.out.println（ \tPersonId整数，）;
用于（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（）;）;
结果：
创建类型PersonAttributePivotType AS（
PersonId整数，
年龄文本，
HairColor文本
）;
函数包装：
创建或替换功能crosstabPersonAttribute（text，text）
返回setofPersonAttributePivotType
AS'$ libdir / tablefunc'，'crosstab_hash'语言C稳定的语法；
自动创建视图：
创建或替换视图PersonAttributePivot AS
SELECT * FROM crosstabPersonAttribute
（
'SELECT PersonId，Key，Value FROM PersonAttribute'，
'SELECT DISTINCT Key FROM PersonAttribute ORDER BY键'
）;
结果：
TABLE PersonAttributePivot;
personid |年龄| haircolor
---------- + ----- + -----------
1 | 27 |
2 | |棕色
（2行）
Consider the example below where I have a Person table containing person records and a PersonAttribute table which contains optional attributes linked to a person:
Table: Person
ID Name 1 Joe Bloggs 2 Jane Doe
Table PersonAttribute
PersonId Key Value 1 Age 27 2 HairColor Brown
How would I write a query that returns all people with the attributes as if they were columns? The resultset I require is:
ID Name Age HairColor 1 Joe Bloggs 27 2 Jane Doe Brown
So essentially I need to write a query that gets all Person Records with all unique Attribute Keys transposed as columns with the value for each person record.
Note that the primary key on the PersonAttribute table is PersonID and Key combined so we wont have duplicate entries for a specific key and person.
Obviously I could add the Age and HairColor as fields in the Person table and not use the PersonAttribute table at all, but this is just an example to illustrate the problem. In reality I have a huge number of custom attributes that vary wildly for different person records so it is not practical to do it that way.
I can't speak about MySQL, but in PostgreSQL you could use crosstab function from tablefunc module:
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
);
Join query:
SELECT id, name, age, haircolor
FROM Person JOIN PersonAttributePivot USING(id)
ORDER BY id;
Wanted result:
id | name | age | haircolor
----+------------+-----+-----------
1 | Joe Bloggs | 27 |
2 | Jane Doe | | Brown
(2 rows)
As you see I put explicit list of columns in PersonAttributePivot view. I don't know any "automatic-pivot" creation way with implicit column list.
EDIT:
For huge column list (assuming always text type) as a workaround I see such little modified approach:
Dynamic type creation (here trivially Java based):
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(");");
Result:
CREATE TYPE PersonAttributePivotType AS (
PersonId integer,
Age text,
HairColor text
);
Function wrapper:
CREATE OR REPLACE FUNCTION crosstabPersonAttribute(text, text)
RETURNS setof PersonAttributePivotType
AS '$libdir/tablefunc','crosstab_hash' LANGUAGE C STABLE STRICT;
Automatic view creation:
CREATE OR REPLACE VIEW PersonAttributePivot AS
SELECT * FROM crosstabPersonAttribute
(
'SELECT PersonId, Key, Value FROM PersonAttribute',
'SELECT DISTINCT Key FROM PersonAttribute ORDER BY Key'
);
Result:
TABLE PersonAttributePivot;
personid | age | haircolor
----------+-----+-----------
1 | 27 |
2 | | Brown
(2 rows)
这篇关于SQL：将行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
