Show month as a row postgresql-CodePudding

I have this table, i call it transaction table

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

I'm trying to achieve this

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

Here is what i do so far

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);

my query above returning this error return and sql tuple descriptions are incompatible

then, i'm using google again and i found different query

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!
    );

but it is returning this error return and sql tuple descriptions are incompatible. How can i solve my problem. thanks in advance

CodePudding user response：

In crosstab, you need to user order by , and give the year column in double quotes

For filtering the month i used 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);