postgresql - 使用动态列名创建数据透视表

Question

我正在创建一个数据透视表，它表示特定年份的崩溃值。目前，我正在对列名进行硬编码以创建数据透视表。有没有办法让列名动态化来创建数据透视表？年份存储在数组中{2018,2017,2016 ..... 2008}

with crash as (
--- pivot table generated for total fatality ---
SELECT * 
FROM crosstab('SELECT b.id, b.state_code, a.year, count(case when a.type = ''Fatal'' then a.type end) as fatality
FROM  '||state_code_input||'_all as a, (select * from source_grid_repository where state_code = '''||upper(state_code_input)||''') as b
where st_contains(b.geom,a.geom) 
group by b.id, b.state_code, a.year
order by b.id, a.year',$$VALUES ('2018'),('2017'),('2016'),('2015'),('2014'),('2013'),('2012'),('2011'),('2010'),('2009'),('2008') $$) 
AS pivot_table(id integer, state_code varchar, fat_2018 bigint, fat_2017 bigint, fat_2016 bigint, fat_2015 bigint, fat_2014 bigint, fat_2013 bigint, fat_2012 bigint, fat_2011 bigint, fat_2010 bigint, fat_2009 bigint, fat_2008 bigint)
)

在上面的代码中，fat_2018、fat_2017、fat_2016 等是硬编码的。我需要 fat_ 之后的岁月充满活力。

Answer 1

这个问题已被问过很多次，并且有不错的(甚至是动态的)解决方案。虽然 CROSSTAB() 在最新版本的 Postgres 中可用，但并不是每个人都有足够的权限来安装必备扩展。

一个这样的解决方案涉及 temp type (temp table) created by an anonymous function & JSON expansion of the resultant type.

另请参阅:DB FIDDLE (英国):https://dbfiddle.uk/Sn7iO4zL

How to pivot or crosstab in postgresql without writing a function?