Transpose a result from one column to another in Postgres

Question

Transpose a result from one column to another in Postgres

Navigation

#1 by (3 votes)
#2 by (1 votes)

3

I have a table similar to the following: (only an example to narrow the problem)

I need to get to this result in which in the current column for the C concept I have the result of the negative B concept from the previous column. Suppose I only have data until Feb , in that case I generate an auxiliary column that I name Mar where I see that logic impacted and Jan will have the results of 2016 .

How could I achieve the result I'm looking for?

This is the SQL that I tried, but it does not give me the expected results:

SELECT year_id, id, concept,
CASE WHEN concept <> 'C' THEN SUM(CASE WHEN month_id = 201701 then value else 0 end) 
                         ELSE SUM(CASE WHEN month_id = 201612 and concept = 'B' then value else 0 end)end as Jan,

CASE WHEN concept <> 'C' THEN SUM(CASE WHEN month_id = 201702 then value else 0 end) 
                         ELSE SUM(CASE WHEN month_id = 201701 and concept = 'B' then value else 0 end)end as Feb,

CASE WHEN concept = 'C' THEN SUM(CASE WHEN month_id = 201701 and concept = 'B' then value else 0 end) else 0 end as Mar
from aux 
group by year_id, id, concept
order by year_id, id, concept;

Demo: link .

sql postgresql

asked by db_ 21.12.2017 в 19:32

source

2 answers

1

Transpose can not be done with pure SQL because in an SQL the number of columns has to be fixed. It can be done with dynamic SQL.

If we knew that the columns are only months the solution with pure SQL could be:

select id, concept, year_id
   , sum(case when month_id=10 then value else null end) as "mes_10"
   , sum(case when month_id=11 then value else null end) as "mes_11"
   , sum(case when month_id=12 then value else null end) as "mes_12"
  from los_datos
  group by id, concept, year_id;

You can see an example running at: link

answered by 21.12.2017 в 20:41

Theory of the grid system css C / C ++ - Pointers

score 3 · Accepted Answer

The following query produces the desired result:

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;

Demo

In the query, I assume that the month_id column contains values from 1 to 12 , instead of the values 201612 , 201701 , 201702 that you have in your question. It seems to me that this makes more sense than including the year in the column that is supposed to contain the month nothing else.

As you can see, the key is in the CTE, where I collect the values as such for the concepts A , B and D , but I completely ignore the records with the concept C . Rather, with the help of UNION ALL , I generate and add virtual concept records C for the next month that are based on the values of the concept records B .

Already with this intermediary query prepared, it is easy to apply a GROUP BY combined with SUM conditional per month. And if you want data for another year, you only need to change the condition in one place, where is WHERE year_id = 2017 .