Pivoting a table using crosstab() in PostgreSQL

Question

I've got this table in Postgres:

create table test("Product id", "Product Name", "Category", "Operator", piece)
as values
 (10,   'Carbonara',    'C1',   'User1',    1)
,(11,   'Spaghetti',    'C1',   'User2',    1)
,(12,   'Coke',         'C2',   'User1',    1)
,(10,   'Carbonara',    'C1',   'User2',    2)
,(11,   'Spaghetti',    'C1',   'User1',    1)
,(11,   'Spaghetti',    'C1',   'User3',    5)
,(12,   'Coke',         'C2',   'User3',    1)
;

I would like to have this result:

Category	User1	User 2	user 3
C1	2	3	5
C2	1	0	1

I made some test with crosstab() function, but didn't get any result.

I've tried crosstab() following some tutorial and answer here on SO, but I didn't understand very well how to create this query.

Answer 1

You can pivot tables using an aggregate filter clause. Add a coalesce() if you prefer to get a 0 when a given Operator has no rows in a given Category (all their rows would get filtered out before sum()):
_{demo at db<>fiddle}

select "Category"
     , coalesce(sum(piece)filter(where "Operator"='User1'),0) as "User1"
     , coalesce(sum(piece)filter(where "Operator"='User2'),0) as "User2"
     , coalesce(sum(piece)filter(where "Operator"='User3'),0) as "User3"
from test
group by "Category";

Category	User1	User2	User3
C1	2	3	5
C2	1	0	1

Using crosstab():

select*from crosstab('select "Category", "Operator", sum(piece) as piece
                      from test group by 1,2 order by 1,2'
                     ,'select distinct "Operator" from test order by 1')
as ("Category" text, "User1" bigint,"User2" bigint,"User3" bigint);