PostgreSQL. Get value from rows and change rows to columns

Question

I have a view which return table like this:

cat_name | Jan | Feb | Mar | ... | YEAR
student  |  0  |  23 | 12  | ... | 2013
student  | 10  |  2  | 8   | ... | 1999
professor| 12  |  9  | 3   | ... | 2015
teacher  |  3  |  8  | 5   | ... | 2015

I need to make view which return smth like this:

cat_name | 1999 | 2013 | 2015 | ...
student  |  20  |  35  |  0   | ...
professor|  0   |   0  |  24  | ...
teacher  |  0   |   0  |  16  | ...

Sum rows and select distinct cat_name:

SELECT cat_name, "YEAR", 
   COALESCE("Jan",0)+COALESCE("Feb",0)+COALESCE("Mar",0)+
   ... +COALESCE("Dec",0) AS sum
FROM view
GROUP BY "YEAR", cat_name, sum;

Is there any way to make "YEAR" values column names? The number of years is not fixed and the number of cat_name too. How can I make some list with yeas from (select distinct "YEAR" from view), for example, and use this years instead 2013, 2014... in CASE WHEN "YEAR" = 2014 ?

Answer 1

You can do this using conditional aggregation:

SELECT cat_name, "YEAR", 
       SUM(CASE WHEN "YEAR" = 2013
                THEN COALESCE("Jan", 0) + COALESCE("Feb", 0) + COALESCE("Mar", 0)+ . . .
           END) as "2013",
       SUM(CASE WHEN "YEAR" = 2014
                THEN COALESCE("Jan", 0) + COALESCE("Feb", 0) + COALESCE("Mar", 0)+ . . .
           END) as "2014",
       . . .
FROM view
GROUP BY cat_name;

If you need a variable number of columns in your output, then you can use a crosstab query. Here is an example.