sql - 使用 Select 语句重组键/值数据

Question

我有一些有效组织的表格，如本例所示:

从广义上讲，有许多“ Assets ”以“标签”的形式拥有一个多对多的键/值对列表，其中包含名称和值。每个 Assets 都可以有任何标签子集，每个 Assets 的每个标签都有一个值。

如果简单地转储显示 Assets 、标签名称和标签值的数据，您最终可能会得到如下结果:

但是，我实际上想要组织数据的方式是这样的:

是否有可能，使用这个架构，而不是能够在这个方法中选择数据？实际上，这涉及将标记表中的每个条目转换成一个列，然后填充在这些列中的每一列中，都包含与每个 Assets 的该标签相关联的值(如果适用)。

This question有完全相同的问题，但由于以下原因，我不能接受它:

1. 关于 Oracle，而我使用的是 PostgreSQL
2. 所有给出的答案都采用引用特定数据名称的硬编码查询，而我要求我的答案动态处理任何数据。
3. 它根本没有足够的浏览量。浏览 37 次？我相信没有任何真正的专家见过这一点。

此外，是否有这种数据选择的名称？我相信这是一个抽象的普遍问题，但我不知道它会被称为什么。

Answer 1

这种设计模式有时称为实体属性值 (EAV)。您可以动态地创建您想要的查询，尽管这有点费力。我不确定有没有办法返回匿名表类型，所以我不得不创建一个函数来创建一个函数:

Create or replace function generatecrosstab() returns void as $BODY$
Declare
    sql varchar := '';
    type varchar := '(assetid int, ';
    sep varchar := '';
    tagid int;
    tname varchar(50);
Begin
    for tagid, tname in select id, tagname from tag order by id
    loop
        sql := sql || sep || 'max(case tagid when ' 
            || tagid::varchar || ' then tagvalue end) ' || tname;
        type := type || sep || tname || ' varchar(255)';
        sep := ', ';
    end loop;
    type := type || ')';
    sql = 'create or replace function crosstab() returns table ' 
       || type || ' as $$' 
       || ' select assetid, ' || sql
       || ' from assettag group by assetid order by assetid;'
       || ' $$ Language sql;';
    execute sql;
End;
$BODY$ Language plpgsql;

如果你执行这个函数，例如:

select generatecrosstab(); 

它将创建一个函数 crosstab()，您可以从中进行选择:

Select * From crosstab();

tablefunc module有一些功能可以简化事情。

SQLFiddle Example