0

I got a view on pgadmin that look like this:

taxCode taxDescription unityCode year month value
300 tax1 1 2020 1 551.5
300 tax1 1 2020 2 200
300 tax1 1 2020 3 303.99
300 tax1 1 2020 4 199.99
... ... ... ... ... ...
6067 tax20 100 2022 1 1012.30

That view contain data of 20 distinct taxes and I wish to "pivot" the view in a way that each taxCode turn into a column:

unityCode month year t300 ... t6067
1 1 2020 551.5 ... null
1 2 2020 200 ... 1002
1 3 2020 303.99 ... null
1 4 2022 199.99 ... null
... ... ... ... ... ...
100 1 2022 null ... 1012.30

I actually got something very close from this using the crosstab function, which was suggested in:

Postgresql crosstab query with multiple "row name" columns

Using the code:

SELECT * 
FROM crosstab(
'SELECT dense_rank() OVER( ORDER BY "unityCode", "month", "year")::int AS row_name
      ,"unityCode", "month", "year"
      ,row_number() OVER (PARTITION BY  "unityCode", "month", "year"
                          ORDER BY "taxCode")::int AS category
      ,"value" AS value
FROM public."view_name"
ORDER BY row_name, category'
, 'VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20)') 
as (row_name int,
        "unityCode" int, 
        "month" int,
        "year" int,
        "t300" numeric,
        "t1201" numeric,
        "t3000" numeric,
        "t3037" numeric,
        "t5720" numeric,
        "t5738" numeric,
        "t5787" numeric,
        "t5804" numeric,
        "t5934" numeric,
        "t5949" numeric,
        "t5963" numeric,
        "t5964" numeric,
        "t5968" numeric,
        "t5970" numeric,
        "t5972" numeric,
        "t5981" numeric,
        "t6015" numeric,
        "t6051" numeric,
        "t6055" numeric,
        "t6067" numeric)

Although, apparently some columns were totally filled with null values (the last one, "t6067", for example). I don't really have much knowledge of the crosstab function, would like to now what is causing this and if there's a way to solve. Thanks in advance.

Eulidio
  • 5
  • 1
  • See [How to do PostgreSQL crosstab query there values are missing in the column](https://stackoverflow.com/a/37424744/1995738) and [Postgres Crosstab allocating values to wrong columns.](https://stackoverflow.com/a/47273672/1995738) – klin Apr 26 '22 at 23:35

0 Answers0