0

I have following table:

|Type | Year | amount |
_______________________
|t1   | 2001 | 40     |
|t1   | 2000 | 50     |
|t2   | 2003 | 30     |
|t2   | 2003 | 20     |
|t3   | 2004 | 10     |

and I would like to show it as: 

   | type |2001 |2000 |2003 |2004|
   |______________________________
   | t1   |40   |50   |0    |0   |
   | t2   | 0   |0    | 50  |0   |
   | t3   |0    |0    | 0   |10  |

I don't want to hard code the years and I need to do that in POstgresql 8.4, which doesn't support:

CREATE EXTENSION
IF NOT EXISTS tablefunc;

I have pivoted table before, using following code: more explanation for the following code is here

 sum(CASE
             WHEN year = 2000 THEN
               total
             ELSE 0
           END)

In which the total =sum (amount) in each year and I had calculated it in another CTE. But at that time the years were already known but for the above table I need to loop through the years and read each and then calculate the sum(amount) and the years may change in the main table.

Ms workaholic
  • 373
  • 2
  • 8
  • 21
  • Possible duplicate of [Postgres simple 'pivot' table](https://stackoverflow.com/questions/12939766/postgres-simple-pivot-table) – jq170727 Sep 12 '17 at 18:19
  • This is different I explained how; Please let me know if it needs more clarification. using that example, the years should be already known, while in my question it's not predetermined and it should be read from table each time – Ms workaholic Sep 12 '17 at 18:35
  • This thread https://stackoverflow.com/questions/36804551/execute-a-dynamic-crosstab-query might be of use. It mentions an article [Automatically creating pivot table column names in PostgreSQL](http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/) in which the author writes `I run PostgreSQL 9.2 but I believe this should work at least as far back as 8.4`. – jq170727 Sep 12 '17 at 19:36
  • Thanks for replying but it doesn't work in version 8.4 – Ms workaholic Sep 12 '17 at 20:04
  • Did you try it? If so, how far did you get? The general approach of running a query to get the years you want and running a query with a bunch of case statements for those years should work for any sql database so I'm curious as to what part of that didn't work. It might be something silly you can fix. – jq170727 Sep 12 '17 at 20:41
  • Yes I tried it on 9 and it worked but when I try it on 8.4 it throws an error for CREATE EXTENSION IF NOT EXISTS tablefunc; – Ms workaholic Sep 12 '17 at 20:53
  • @Verver http://dbfiddle.uk/?rdbms=postgres_8.4&fiddle=66991aae158f19fac7281129fed1948d – Abelisto Sep 14 '17 at 14:55
  • Works great! That's exactly what I want. Thank you Abelisto. I wish you had replied as an Answer so that I can mark it as Answer – Ms workaholic Sep 14 '17 at 15:32

0 Answers0