1

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

JNevill
  • 46,980
  • 4
  • 38
  • 63
Heiko O
  • 47
  • 5
  • sry, this is my 1st post here. pls click on the image link for details. thx – Heiko O Sep 18 '18 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 Sep 18 '18 at 14:45
  • hi, there will be more class ids, so yes this should be dynamic – Heiko O Sep 18 '18 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 Sep 18 '18 at 14:46
  • I noticed ;-), that's kind of you, appreciate it ! – Heiko O Sep 18 '18 at 14:49
  • 1
    Dynamic Pivot in Postgres is not at all easy. There is a GREAT [write up at this question](https://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334) 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 Sep 18 '18 at 14:49
  • Generally dynamic pivot/crosstab output is best pushed to your application layer (if one exists). This is a place that client reporting software excels (like tableau, business objects(?I think), excel, etc...) – JNevill Sep 18 '18 at 14:52
  • thx, what would the static solution look like, say with only 5 classids. I am using Pentaho Kettle - this is where all the ETL is happening.... – Heiko O Sep 18 '18 at 14:55
  • Sorry for the delay. [This is a good simple example of a static crosstab](https://stackoverflow.com/questions/3002499/postgresql-crosstab-query) – JNevill Sep 18 '18 at 15:27

1 Answers1

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
S-Man
  • 22,521
  • 7
  • 40
  • 63