我有一个名为“ ward_councillors”的表，以及两列“ ward”和“ councillor”。该表包含所有议员和他们所负责的病房的列表。每个病房都有三名议员，我需要按小组进行分组，这样我每个病房都有一个独特的记录，并且有三个新的栏目，每个病房中都有三名议员负责。
例如当前有：
WARD | COUNCILLOR ward a | cllr 1 ward a | cllr 2 ward a | cllr 23 ward b | cllr 4 ward b | cllr 5 ward b | cllr 8
试图达到：
WARD | COUNCILLOR 1| COUNCILLOR 2| COUNCILLOR 3 ward a | cllr 1 | cllr 2 | cllr 23 ward b | cllr 4 | cllr 5 | cllr 8
预先感谢克里斯
我倾向于使用条件聚合来处理这种类型的查询：
select ward, max(case when seqnum = 1 then councillor end) as councillor1, max(case when seqnum = 2 then councillor end) as councillor2, max(case when seqnum = 3 then councillor end) as councillor3 from (select wc.*, row_number() over (partition by ward order by councillor) as seqnum from ward_councillors wc ) wc group by ward;
