sql - Turning arbitrarily many rows into columns in PostgreSQL

Question

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.

Answer 1

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?