Crosstab in Postgres for several fields

Question

Postgres 13 on Windows. Let's assume I have a table like this:

Job_type Department User_id Op_code Times_executed ------------------------------------------------------------------------------------- ACCOUNTING MANAGER Accounting Alice VA01 578 ACCOUNTING MANAGER Accounting Alice FBL1N 2256 ACCOUNTING MANAGER Accounting Alice FBL3N 16272 ACCOUNTING MANAGER Accounting Alice ME23N 1682 ACCOUNT ASSISTANT General Bob ME2L 876 ACCOUNT ASSISTANT General Bob VA04 25700 ACCOUNT ASSISTANT General Bob VF77 12393 ACCOUNT ASSISTANT General Bob MEXX 5182

I'd like to use the crosstab function in Postgres to get a pivot table like this:

Job_type Department User_id Op1 times1 Op2 times2 Op3 times3 Op4 times4 ACCOUNTING MANAGER Accounting Alice VA01 578 FBL1N 2256 FBL3N 16272 ME23N 1682 ACCOUNT ASSISTANT General Bob ME2L 876 VA04 25700 VF77 12393 MEXX 5182

I tried with my basic knowledge of crosstab but I only managed to get the operation codes, but not the times:

SELECT * FROM crosstab( 'select user_id, job_type, tcode from mytable order by 1,2') AS ct(row_name text, category_1 text, category_2 text, category_3 text, category_4 text);

Is it possible to get it all in the format above?

Thanks a lot in advance.

Can you share the code you tried ? Do you know in advance the maximum number of op_codes per user_id ? — Edouard
– Edouard, Commented Mar 21, 2022 at 15:26
@Edouard I have added the code, but in this case I'm only able to get the user_id and 4 op_codes. I know that there is a maximum of 20 codes, but the text can have many different values, so I cannot use the op_code text as a category itself. Thank you! — Carpo Crates
– Carpo Crates, Commented Mar 21, 2022 at 15:48

Edouard · Accepted Answer · 2022-03-22 13:46:35Z

here is an alternative solution to the crosstab() function :

You create a composite type which corresponds to the list of columns to be displayed :

CREATE TYPE new_type AS (op1 text, times1 integer, op2 text, times2 integer, op3 text, times3 integer, op4 text, times4 integer, op5 text, times5 integer, op6 text, times6 integer, op7 text, times7 integer, op8 text, times8 integer, op9 text, times9 integer, op10 text, times10 integer, op11 text, times11 integer, op12 text, times12 integer, op13 text, times13 integer, op14 text, times14 integer, op15 text, times15 integer, op16 text, times16 integer, op17 text, times17 integer, op18 text, times18 integer, op19 text, times19 integer, op20 text, times20 integer)

The following query will provide the expected result :

SELECT Job_type, Department, User_id , (jsonb_populate_record(null :: new_type, jsonb_object_agg(key1, val1) || jsonb_object_agg(key2, val2))).* FROM ( SELECT Job_type, Department, User_id , 'op' || row_number () OVER w AS key1 , Op_code AS val1 , 'times' || row_number () OVER w AS key2 , Times_executed AS val2 FROM my_table AS t WINDOW w AS (PARTITION BY Job_type, Department, User_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ) AS a GROUP BY Job_type, Department, User_id

see the test result in dbfiddle.

Collectives™ on Stack Overflow

Crosstab in Postgres for several fields

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related