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