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
![PDF怎么转换成word 这两种简单的PDF转word方法可以收藏](https://p.xsw88.cn/allimgs/daicuo/20230907/6331.png)
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?
相关推荐
最新文章