PostgreSQL Question posted in
The official documentation can be found here.

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.

Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    Alternatively you can pivot into a single JSON object instead of several (number may vary) columns.

    select "Category", json_object_agg("Operator", s) users
from
(
 select "Category", "Operator", sum(piece) as s
 from test group by "Category", "Operator"
) as t
group by "Category";
    Category users
    C1 { "User2" : 3, "User3" : 5, "User1" : 2 }
    C2 { "User3" : 1, "User1" : 1 }

    DB Fiddle demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search