在 PostgreSQL 中将任意多行转换为列 [英] Turning arbitrarily many rows into columns in PostgreSQL

问题描述

I have a table in Postgres that was designed to capture information in unstructured form and rebuild it. I need to re-apply some structure when exporting data from that table and am struggling.

Currently, I have a table of the form:

lbl |   name     |  value
----|------------|--------
1   | num        |       1
1   | colour     |   "Red"
1   | percentage |    25.0
2   | num        |       2
2   | colour     | "Green"
2   | percentage |    50.0
3   | num        |       3
3   | colour     |  "Blue"
3   | percentage |    75.0

And I need to generate a table in this form:

lbl | num |  colour | percentage
----|-----|---------|------------
1   | 1   | "Red"   |   25.0
2   | 2   | "Green" |   50.0
3   | 3   | "Blue"  |   75.0

I have built this query:

SELECT lbl, 
   max(case when name = 'num' then value else '-' end) num,
   max(case when name = 'colour' then value else '-' end) colour,
   max(case when name = 'percentage' then value else '-' end) percentage
FROM example_table
GROUP BY lbl

The query works but I need to expand it to include an arbitrary number of potential values for name. I have investigated crossfunc but was unable to get it to work as I intended. Any help would be greatly appreciated.

I've set up an sqlfiddle here to help kick things off: http://sqlfiddle.com/#!9/8d3133/6/0

edit: I can use PL/pgSQL also if that makes it possible.

解决方案

The main problem with pivot tables in Postgres (and other RDBMS) is that the structure (number and names of columns) of a query result 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)

Test it in db<>fiddle.

Note, that it's necessary to recreate a view (call the function) only after a 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.

You may be also interested in Flatten aggregated key/value pairs from a JSONB field?

这篇关于在 PostgreSQL 中将任意多行转换为列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！