PosrgreSQL Pivot Table

Question

I need to make a PIVOT table from Source like this table

FactID  UserID  QTY Product
1         10    100   A
2         10    200   B
3         10    300   C
4         12    50    A
5         12    60    B
6         12    70    C
7         15    500   A
8         15    550   B
9         15    600   C

Need Pivot Like this

UserID  A   B   C
10     100  200 300
12     50   60  70
15     500  550 600

My try

Select UserID,
       CASE WHEN product = 'A' then QTY end as A,
       CASE WHEN product = 'B' then QTY end as B,
       CASE WHEN product = 'C' then QTY end as C
from public.table

And Result

UserID  A   B   C
10     100  100 100
10     200  200 200
10     300  300 300
12      50  50  50
12      60  60  60
12      70  70  70
15      500 500 500
15     550  550 550
15     600  600 600

Where's my mistake? Maybe there's another way to do it?

Answer 1

You need aggregate function as

Select UserID,
       Max(CASE WHEN product = 'A' then QTY end) as A,
       Max(CASE WHEN product = 'B' then QTY end) as B,
       Max(CASE WHEN product = 'C' then QTY end) as C
from public.table
Group by userid

Answer 2

Very close. You just need aggregation:

Select UserID,
       SUM(CASE WHEN product = 'A' then QTY end) as A,
       SUM(CASE WHEN product = 'B' then QTY end) as B,
       SUM(CASE WHEN product = 'C' then QTY end) as C
from public.table
group by UserId;

In Postgres, though, this would normally use the FILTER clause instead of CASE:

Select UserID,
       SUM(qty) FILTER (WHERE product = 'A') as A,
       SUM(qty) FILTER (WHERE product = 'B') as B,
       SUM(qty) FILTER (WHERE product = 'C') as C
from public.table
group by UserId;