how to merge two rows in postgres using select [duplicate]

Question

I have a table that has rows that look like this

Account	Currency	Amount
1	USD	5
1	EUR	10
2	USD	8
3	EUR	4

Is there a way to do a select query so it would return something like

Account	amount_usd	amount_eur
1	5	10
2	8
3		4

usd and eur are hardcoded, so i dont have to build the name from the column.

This is well explained in this question: stackoverflow.com/questions/23060256/… — rodolfo_r
– rodolfo_r, Commented Mar 1, 2021 at 20:00
Does this answer your question? Postgres - Transpose Rows to Columns — astentx
– astentx, Commented Mar 1, 2021 at 21:16

Gordon Linoff · Accepted Answer · 2021-03-01 19:59:54Z

1

You can use conditional aggregation which in Postgres uses filter:

select account,
       sum(amount) filter (where currency = 'USD') as usd_amount,
       sum(amount) filter (where currency = 'EUR') as eur_amount
from t
group by account;

answered Mar 1, 2021 at 19:59

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-01 20:02:16Z

0

You can use sum() with case when condition :

select account, sum(case when currency='USD' then amount end) Amount_USD,
sum(case when currency='EUR' then amount end) Amount_EUR
from myTable
Group by account

answered Mar 1, 2021 at 20:02

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Collectives™ on Stack Overflow

how to merge two rows in postgres using select [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related