将月份显示为一行postgresql

Question

我有这个表，我称它为事务表

id  periode_month   total_amount
U1       1           1000
U1       2           1200
U1       3           1000
U1       4           1000
U2       2           1250

我正在努力实现这个

id  month 1 month 2 month 3 month 4 month 5 ... month 12
U1  1000    1200    1000    1000      0           0
U2   0      1250      0       0       0           0

以下是我到目前为止所做的

SELECT *
FROM crosstab(
'select client_id, periode_month, total_amount
from sucor_transactions
order by 1,2')
AS ct(userid VARCHAR, periode_month int, total_amount numeric);

我上面的查询返回了这个错误return and sql tuple descriptions are incompatible

然后，我再次使用谷歌，我发现了不同的查询

SELECT *
FROM   crosstab (
$$SELECT client_id, periode_month,"total_amount"
FROM   sucor_transactions
ORDER  BY 1,2$$
) AS t (
class int
-- "value" double precision  -- column does not exist in result!
);

但是它返回这个错误CCD_ 2。我怎样才能解决我的问题。提前感谢

Answer 1

在交叉表中，您需要根据用户订单，并用双引号给出年份列

对于筛选月份，我使用generate series。

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB (
'SELECT id,periode_month,total_amount
FROM crospost order by 1,2' ,'SELECT g FROM generate_series(1,12) g') 
AS ct(id varchar , "month 1" int, "month 2" int, "month 3" int, 
"month 4" int, "month 5" int, "month 6" int,
"month 7" int, "month 8" int, "month 9" int,
"month 10" int, "month 11" int, "month 12" int);