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 ?

The result of a select has a fixed number of columns - independent of table data. — jarlh
– jarlh, Commented Oct 16, 2015 at 11:00

Community · Accepted Answer · 2017-05-23 11:44:48Z

0

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.

edited May 23, 2017 at 11:44

CommunityBot

11 silver badge

answered Oct 16, 2015 at 11:00

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Svetlana Over a year ago

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 ?

Collectives™ on Stack Overflow

PostgreSQL. Get value from rows and change rows to columns

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related