transponer resultado, PostgreSQL

Language: Layout:

create table aux (
    month_id int, 
    year_id int, 
    id int, 
    concept varchar(1), 
    value int
);

insert into aux values
  (12, 2016, 1234, 'A', 1),
  (12, 2016, 1234, 'B', 2),
  (12, 2016, 1234, 'C', 0),
  (12, 2016, 1234, 'D', 3),
  (12, 2016, 1234, 'A', 1),
  (12, 2016, 1234, 'B', 2),
  (12, 2016, 1234, 'C', 0),
  (12, 2016, 1234, 'D', 3),
  (1, 2017, 1234, 'A', 2),
  (1, 2017, 1234, 'B', 3),
  (1, 2017, 1234, 'C', 0),
  (1, 2017, 1234, 'D', 4),
  (1, 2017, 1234, 'A', 2),
  (1, 2017, 1234, 'B', 3),
  (1, 2017, 1234, 'C', 0),
  (1, 2017, 1234, 'D', 4),
  (2, 2017, 1234, 'A', 5),
  (2, 2017, 1234, 'B', 6),
  (2, 2017, 1234, 'C', 0),
  (2, 2017, 1234, 'D', 1),
  (2, 2017, 1234, 'A', 5),
  (2, 2017, 1234, 'B', 6),
  (2, 2017, 1234, 'C', 0),
  (2, 2017, 1234, 'D', 1);

with cte as (
	select month_id,
	       year_id,
	       id,
	       concept,
	       value
	  from aux
	 where concept in ('A', 'B', 'D')
	 union all
	select case when month_id = 12 then 1 else month_id + 1 end as month_id,
	       case when month_id = 12 then year_id + 1 else year_id end as year_id,
	       id,
	       'C' as concept,
	       value * -1 as value
	  from aux
	 where concept = 'B'
)
select id,
       concept,
       year_id,
       coalesce(sum(case when month_id = 1 then value end),0) as jan,
       coalesce(sum(case when month_id = 2 then value end),0) as feb,
       coalesce(sum(case when month_id = 3 then value end),0) as mar
  from cte
 where year_id = 2017 -- escoges el año que quieres aquí
 group by id,
          concept,
          year_id
 order by id,
          concept;


absolute service time: 0,39 sec