超过 1 列具有 string_agg 的唯一值

我正在尝试分组并获取多列的值列表。这是一个例子：

City   | State | Income
-------+-------+--------
Salem  |  OH   | 40000
Salem  |  OH   | 45000
Mason  |  OH   | 50000
Dayton |  OH   | 60000
Salem  |  MA   | 40000
Mason  |  MA   | 45000
Mason  |  MA   | 50000
Dayton |  MA   | 70000
Salem  |  PA   | 45000
Mason  |  PA   | 50000
Dayton |  PA   | 60000

我正在寻找的结果是：

City   |  States    | Income
-------+------------+--------------
Salem  | OH,MA,PA   | 40000,45000
Mason  | OH,MA,PA   | 50000,45000
Dayton | OH,MA,PA   | 60000,70000

我设法做到了这一点：

City   |  States    | Income
-------+------------+-------------------------
Salem  | OH,MA,PA   | 40000,40000,45000,45000
Mason  | OH,MA,PA   | 50000,50000,50000,45000
Dayton | OH,MA,PA   | 60000,70000,60000

我如何从这里转到结果集？

City   |  States    | Income
-------+------------+-------------------------
Salem  | OH,MA,PA   | 40000,45000,50000
Mason  | OH,MA,PA   | 50000,45000
Dayton | OH,MA,PA   | 60000,70000

唉，你不能使用string_agg() with distinct。但您可以使用条件聚合：

select city,
       string_agg(case when seqnum_state = 1 then state end, ',') as states,
       string_agg(case when seqnum_income = 1 then income end, ',') as incomes
from (select t.*,
             row_number() over (partition by city, state order by state) as seqnum_state,
             row_number() over (partition by city, income order by income) as seqnum_income
      from t
     ) t
group by city;

是一个数据库小提琴。