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.

Answer 1

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.