Postgres中数据透视表的主要问题是查询的结果结构（列数和列名）不能根据所选数据而变化。可能的解决方案之一是动态创建视图，该视图由数据定义。示例函数根据表 example_table 创建一个视图：

The main problem with pivot tables in Postgres is that the result structure (number and names of columns) of a query cannot vary depending on the selected data. One of the possible solutions is to dynamically create a view, which structure is defined by the data. The example function creates a view based on the table example_table:

create or replace function create_pivot_view()
returns void language plpgsql as $$
declare
    list text;
begin
    select string_agg(format('jdata->>%1$L "%1$s"', name), ', ')
    from (
        select distinct name
        from example_table
        ) sub
    into list;

    execute format($f$
        drop view if exists example_pivot_view;
        create view example_pivot_view as
        select lbl, %s
        from (
            select lbl, json_object_agg(name, value) jdata
            from example_table
            group by 1
            order by 1
            ) sub
        $f$, list);
end $$;

在修改表后（可能在触发器中）使用该函数并查询创建的视图：

Use the function after the table is modified (maybe in a trigger) and query the created view:

select create_pivot_view();

select *
from example_pivot_view;

 lbl | num | colour | percentage 
-----+-----+--------+------------
   1 | 1   | Red    | 25.0
   2 | 2   | Green  | 50.0
   3 | 3   | Blue   | 75.0
(3 rows)

在此处进行测试。

请注意，只有在新建视图之后才需要重新创建视图（调用函数）名称被添加到表中（或从中删除了一些名称）。如果唯一名称集不变，则可以查询视图而无需重新创建。如果经常修改集合，则创建临时视图将是更好的选择。

Note, that it's necessary to recreate a view (call the function) only after new name is added to the table (or some name is removed from it). If the set of distinct names doesn't change you can query the view without recreating it. If the set is modified frequently creating a temporary view would be a better option.

您可能还对从JSONB字段中平整的聚合键/值对？