There is a table with prices
city_id product_id price
1 1 1
1 2 2
1 3 3
2 1 4
2 2 5
2 3 6
Is it possible to show cities as columns?
procuct_id city_1 city_2 ... city_n
1 1 4
2 2 5
3 3 6
You just need to do pivot as following. here is the demo.
select
product_id,
max(case when city_id = 1 then price end) as city_1,
max(case when city_id = 2 then price end) as city_2,
max(case when city_id = 3 then price end) as city_3,
.
.
max(case when city_id = n then price end) as city_n
from myTable
group by
product_id
Output:
| product_id | city_1 | city_2 | | city_n |
| ---------- | ------ | ------ |...... |------- |
| 1 | 1 | 4 | | |
| 2 | 2 | 5 | | |
| 3 | 3 | 6 | | |
I would recommend conditional aggregation for this. Postgres supports the (standard and) very handy filter clause:
select product_id,
max(price) filter (where city_id = 1) as city_1,
max(price) filter (where city_id = 2) as city_2,
max(price) filter (where city_id = 3) as city_3
from myTable
group by product_id;
The only caveat is: If you don't know the specific cities you want, then you need some sort of dynamic SQL.
