sqlpostgresqlpivot-tablepostgres-crosstab

Pivoting a table using crosstab() in PostgreSQL


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.


Solution

  • 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);