How to Use Crosstab with dynamic number of columns by making use of Join Clause In PostgreSQL

Question

I am trying to get Table Data in a vertical way using Crosstab. To be more precise, I need to group by prefix and postfix at the same time and get summ. In this case I should Join 2 tables and get names for unique prefix and postfix. Number of Columns may vary depending on income_account_setting table values. In my case I have more than 20 different names meaning that I should have code_account and 20 columns. I tried to do with the help of Crosstab, But on the internet examples are given for fixed number of columns

My Tables: main_client_profit

id     |  code_account      | summa       | prefix     | postfix  |   oper_month   |
------------------------------------------------------------------------------------
1      |  04315187          | 72871232.77 | 45233      |  222     |  2020-04       |
2      |  00524154          | 89753426.58 | 45294      |  100     |  2020-01       |
3      |  02115187          | 23224345.89 | 45249      |  204     |  2020-03       |
4      |  00388996          | 119030.87   | 45294      |  246     |  2020-05       |
5      |  04222959          | 105000.00   | 45233      |  222     |  2020-04       |
6      |  04315187          | 92871232.77 | 45233      |  222     |  2020-04       |
1      |  04222959          | 1020458.40  | 45233      |  421     |  2020-01       |

income_account_setting

id    |  prefix    |  postfix   |  name          |
-------------------------------------------------------------------------------------
1     |  45233     |  222       |   A            |
1     |  45294     |  100       |   B            |
1     |  45249     |  204       |   C            |
1     |  45294     |  246       |   E            |
1     |  45233     |  421       |   F            |

The result I need to take

------------------------------------------------------------------------------------
code_account      | A         | B           |  C          |  D   |    E  |
----------------------------------------------------------
04315187          | 165742465 | 0           |  0          | 0    |   0   |
00524154          |           | 89753426.58 |  0          | 0    |   0   |
02115187          |    0      |             | 23224345.89 | 0    |   0   |

and etc ....

What I tried so far but it does not work

SELECT *
FROM crosstab('SELECT m.code_account, a.name, evaluation_result 
        FROM main_client_profit m
             INNER JOIN income_account_setting a 
            ON m.prefix = a.prefix AND m.postfix = a.postfix
        GROUP BY CONCAT(m.prefix,m.postfix)
        ORDER BY 1,2') 
   AS final_result(code character varying(20),)

In above code I don`t know what to write inside final_result as columns.

Is it possible to do this task with Crosstab? Or is there any other way to do it Any help is appreciated.

Unrelated to your question, but `GROUP BY CONCAT(m.prefix,m.postfix)` is a bad idea, as it will result in wrong grouping. It will treat `12,1` the same as `1,21` which is not what you want. You should use group by all columns individually: `group by m.prefix, m.postfix` — , Jul 08 '20 at 11:00

score 1 · Answer 1 · answered Jul 08 '20 at 10:54

My opinion is, that the crosstab() function typically complicates things more then it helps. And it can't overcome the limitation that all columns of the result must be known before the statement is run.

I would do it like this:

SELECT m.code_account,
       sum(m.summa) filter (where a.name = 'A') as "A",
       sum(m.summa) filter (where a.name = 'B') as "B",
       sum(m.summa) filter (where a.name = 'C') as "C",
       sum(m.summa) filter (where a.name = 'D') as "D",
       sum(m.summa) filter (where a.name = 'E') as "E"
FROM main_client_profit m
  LEFT JOIN income_account_setting a
         ON m.prefix = a.prefix 
        AND m.postfix = a.postfix
GROUP BY m.code_account
ORDER BY m.code_account

Thank you for help. This answer is more simpler compared to crosstab, but when I add new data into ```income_account_setting``` then I also need to update query as well. — Abdusoli, Jul 08 '20 at 11:25
@Abdusoli: that's correct, and using `crosstab()` wouldn't solve that problem. It's an intrinsic limitation of SQL that the number of resulting columns must be known before the database runs the statement. — , Jul 08 '20 at 11:37

wildplasser · Answer 2 · 2020-07-08T14:07:14.750

You can pack/unpack using JSON, but you need to restore the correct datatype.

[this is not truly dynamic, you still have to type in the list of column names. Thus could be automated wih dynamic SQL]

WITH omg AS ( -- Pack into json
        SELECT m.code_account
        , json_object_agg(a.name, summa) AS zooi
        FROM main_client_profit m
        JOIN income_account_setting a
        ON m.prefix = a.prefix AND m.postfix = a.postfix
        GROUP BY m.code_account
        ORDER BY 1
        )
SELECT o.code_account -- And unpack it. Still need to cast to the wanted type
        , (o.zooi->>'A')::DECIMAL(10,2) AS A
        , (o.zooi->>'B')::DECIMAL(10,2) AS B
        , (o.zooi->>'C')::DECIMAL(10,2) AS C
        , (o.zooi->>'D')::DECIMAL(10,2) AS D
        , (o.zooi->>'E')::DECIMAL(10,2) AS E
        , (o.zooi->>'F')::DECIMAL(10,2) AS F
FROM omg o
ORDER BY 1
        ;

How to Use Crosstab with dynamic number of columns by making use of Join Clause In PostgreSQL

2 Answers2