# Transpose a result from one column to another in Postgres

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;
``````

asked by db_ 21.12.2017 в 19:32
source

3

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;
``````

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` .

source
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
{"ft_lang_label":"__label__en","ft_lang_prob":0.89038694,"math_prob":0.9294038,"size":1101,"snap":"2020-45-2020-50","text_gpt3_token_len":292,"char_repetition_ratio":0.16681859,"word_repetition_ratio":0.25592417,"special_character_ratio":0.2897366,"punctuation_ratio":0.08370044,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.99408096,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-11-26T00:21:31Z\",\"WARC-Record-ID\":\"<urn:uuid:7779f13c-58a3-41f2-8398-582d8ae2b257>\",\"Content-Length\":\"17714\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:344e4dbb-7962-4702-96ca-094864c8cfe5>\",\"WARC-Concurrent-To\":\"<urn:uuid:6d80501d-a65d-41c7-b3c3-1f2f37e6d3af>\",\"WARC-IP-Address\":\"172.67.145.60\",\"WARC-Target-URI\":\"https://ifelse.info/questions/2005/transpose-result-one-column-another-postgres\",\"WARC-Payload-Digest\":\"sha1:T3F477MO2ZWFGVLL4D5LWHXEZMHM2IIK\",\"WARC-Block-Digest\":\"sha1:ZJB77ISAXQZI2OE7LIHCWV2O63IRTW5F\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-50/CC-MAIN-2020-50_segments_1606141185851.16_warc_CC-MAIN-20201126001926-20201126031926-00600.warc.gz\"}"}