Create Postgres Pivot like excel

Question

I need help with creating a pivot table. I've already tried many offered solutions here but I am stuck. Hope someone can help. Here's my scenario:

Table1 sample

ID              text    2712cb957
document_key    text    201808

Table2 sample

ID          text    2712cb957
amount_net  int     56000
classid     int     4021

Table3 (from Table1 and Table2 via inner join ID)
document_key amount_net classid

201808          10          4021
201808          10          4021
201808          15          4023
201808          13          4023
201807          20          4021
201807          15          4021
…               …           …

Expected pivot table

document_key    4021    4023
201807          35      0
201808          20      28

enter image description here

sry, this is my 1st post here. pls click on the image link for details. thx — Heiko O
– Heiko O, Commented Sep 18, 2018 at 14:44
Do you know in advance that you just want 4021 and 4023? Or will this be dynamic based on however many distinct classid exist in your table? — JNevill
– JNevill, Commented Sep 18, 2018 at 14:45
hi, there will be more class ids, so yes this should be dynamic — Heiko O
– Heiko O, Commented Sep 18, 2018 at 14:46
No worries on the formatting on all that. To preserve your whitespace and line feeds, just indent each line of your tables/code with 4 spaces. You can also highlight the block and hit the {} button at the top of the editor. I've made the edit to this question to do just that. — JNevill
– JNevill, Commented Sep 18, 2018 at 14:46
Dynamic Pivot in Postgres is not at all easy. There is a GREAT write up at this question though that walks you through the crosstab keyword, how to apply it, and the steps necessary to make something that is dynamic. It's one of those things where you either dive into functions, or you generate a crosstab/pivot query dynamically and then execute the dynamically generated sql. — JNevill
– JNevill, Commented Sep 18, 2018 at 14:49

S-Man · Accepted Answer · 2018-09-19 08:46:58Z

0

demo:db<>fiddle

A static pivot could look like this:

Postgres 9.4+

Postgres 9.4 adds the FILTER function:

SELECT 
    document_key, 
    COALESCE(SUM(amount_net) FILTER (WHERE classid = 4021), 0) as "4021", -- A
    COALESCE(SUM(amount_net) FILTER (WHERE classid = 4023), 0) as "4023"
FROM test 
GROUP BY document_key
ORDER BY document_key

A: GROUP BY document_key aggregates all document_key groups. With the aggregate function SUM you add all corresponding amount_net values. The FILTER function is used to sum only these values from the rows containing classid = XYZ. The COALESCE around the aggregate makes NULL values to 0.

If you want more columns you have to copy this line of code.

Postgres 9.3 and lower

SELECT 
    document_key, 
    SUM(CASE WHEN classid = 4021 THEN amount_net ELSE 0 END) as "4021", 
    SUM(CASE WHEN classid = 4023 THEN amount_net ELSE 0 END) as "4023"
FROM test 
GROUP BY document_key
ORDER BY document_key

edited Sep 19, 2018 at 8:46

answered Sep 18, 2018 at 18:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Heiko O Over a year ago

Thanx, your sample throws a syntax error at the first occurance of FILTER

S-Man Over a year ago

Which version are you using?

Heiko O Over a year ago

we're using 9.3

S-Man Over a year ago

You should really upgrade your database since 9.3 is not longer supported. And you miss many really great features. Nevertheless: Added a more compatible version of the pivot and updated the fiddle

Heiko O Over a year ago

and how do I do that?

|

Collectives™ on Stack Overflow

Create Postgres Pivot like excel

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related