你只需要按以下步骤做。这是演示。

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

输出：

| product_id | city_1 | city_2 |       | city_n |
| ---------- | ------ | ------ |...... |------- |
| 1          | 1      | 4      |       |        |
| 2          | 2      | 5      |       |        |
| 3          | 3      | 6      |       |        |

我建议对此进行条件聚合。postgres支持（标准和）非常方便 filter 条款：

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;

唯一的警告是：如果您不知道您想要的具体城市，那么您需要某种动态sql。