SQL / PostgreSQL How to do a query that merge values from some columns into one row

Question

There is a table:

[region]	[country]	[month]	[VAL]	[VAL_PY]
EU	Poland	January	x	a
EU	Poland	January	x1	a2
EU	Poland	February	xx	aa
EU	France	January	y	b
NA	Brazil	February	z	c
...	...	...	...	...

there is a need to make another table based on the one above, such as the one below, one row for one country, 12 new columns = 12 months, the value in each country/month is the KPI:

[region]	[country]	[January]	February]	...	[December]
EU	Poland	KPI	KPI	...	KPI
EU	France	KPI	KPI	...	KPI
...	...	KPI	KPI	...	KPI

The KPI code:

CASE WHEN  sum(A."value_py")=0 THEN 0 
else round(((sum("value")-sum("value_py"))/sum("value"))*100,1 
END

Please help me, I can find the solution where there are duplicates in every country which is not acceptable

How many months do you need to push out columnwise?

Ross Bush
– Ross Bush

2021-04-13 13:12:10 +00:00
Commented Apr 13, 2021 at 13:12 — Ross Bush
– Ross Bush, Commented Apr 13, 2021 at 13:12
all, 12 months.

Piotr
– Piotr

2021-04-13 13:13:03 +00:00
Commented Apr 13, 2021 at 13:13 — Piotr
– Piotr, Commented Apr 13, 2021 at 13:13
the KPI should be calculated for every month separately

Piotr
– Piotr

2021-04-13 13:13:57 +00:00
Commented Apr 13, 2021 at 13:13 — Piotr
– Piotr, Commented Apr 13, 2021 at 13:13

The Impaler · Accepted Answer · 2021-04-13 13:32:20Z

1

You are pivoting the data. You can use FILTER to retrieve data for each specific column.

For example:

select
  region,
  country,
  case when sum(a.val_py) filter(where month = 'January') = 0 then 0 
       else round(((sum(val) filter(where month = 'January') - 
            sum(val_py) filter(where month = 'January')) / 
            sum(val_py) filter(where month = 'January')) * 100, 1) 
  end as january,
  case when sum(a.val_py) filter(where month = 'February') = 0 then 0 
       else round(((sum(val) filter(where month = 'February') - 
            sum(val_py) filter(where month = 'February')) / 
            sum(val_py) filter(where month = 'February')) * 100, 1) 
  end as february,
  -- add the rest of the months here...
from t
group by region, country

edited Apr 13, 2021 at 13:32

answered Apr 13, 2021 at 13:19

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

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

1 Comment

The Impaler Over a year ago

@Piotr It was a typo. It should say GROUP BY. Fixed.

Collectives™ on Stack Overflow

SQL / PostgreSQL How to do a query that merge values from some columns into one row

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related