Postgresql 行变列 crosstab交叉函数 报错及解决；No function matches the given name and arguments.

Postgresql行变列 使用crosstab

需要安装拓展：extension

1. 报错

2. 解决

1. 安装扩展

CREATE EXTENSION tablefunc WITH SCHEMA <<YourSchema>>;

2. order by 1也需要，很重要~~保证同一条记录能合并，不加order by 效果如下
   可以发现不加order 同样的记录未合并；
   

   加了效果图如下：
   

3. 源码

转载自https://blog.csdn.net/moshowgame/article/details/127469331

-- by zhengkai.blog.csdn.net
create table sales(year int, month int, qty int);
insert into sales values(2022, 1, 1000);
insert into sales values(2022, 2, 1500);
insert into sales values(2022, 7, 500);
insert into sales values(2022, 11, 1500);
insert into sales values(2022, 12, 2000);
insert into sales values(2023, 1, 1200);

select * from crosstab(
  'select year, month, qty from sales order by 1',
  'select m from generate_series(1,12) m'
) as (
  year int,
  "Jan" int,
  "Feb" int,
  "Mar" int,
  "Apr" int,
  "May" int,
  "Jun" int,
  "Jul" int,
  "Aug" int,
  "Sep" int,
  "Oct" int,
  "Nov" int,
  "Dec" int
);
 year | Jan  | Feb  | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov  | Dec
------+------+------+-----+-----+-----+-----+-----+-----+-----+-----+------+------
 2022 | 1000 | 1500 |     |     |     |     | 500 |     |     |     | 1500 | 2000
 2023 | 1200 |      |     |     |     |     |     |     |     |     |      |
(2 rows)
————————————————

参考

• https://blog.csdn.net/moshowgame/article/details/127469331