在Oracle中，这是使用keep语法聚合函数的一个很好的用例：

select caseid, name, 
    min(address) keep(dense_rank first order by address) address,
    min(zip)     keep(dense_rank first order by address) zip,
    listagg(role, ', ') within group (order by role) role
from mytable
group by caseid, name

keep的order by子句允许您一致地“挑选”组中的一行--在与组中的其他行进行排序时，其address排在第一位的行;我们可以跨列重复该表达式以获得同一行的zip。
请注意，使用更稳定的排序条件（例如可能是order by address_id？）将有利于查询。否则，当存在重复地址时，应 * 保留 * 哪一行的zip？

这里 有 一 个 选项 ;读取 代码 中 注解 。
示例 数据 ：

SQL> with test (caseid, name, address, zip, role) as
  2    (select 1, 'Joe' , 'address_1', 'zip_1', 'role_1' from dual union all
  3     select 1, 'John', 'address_2', 'zip_2', 'role_1' from dual union all
  4     select 1, 'Bill', 'address_4', 'zip_4', 'role_1' from dual union all
  5     select 1, 'Bill', 'address_5', 'zip_5', 'role_2' from dual union all
  6     select 2, 'Bob' , 'address_6', 'zip_6', 'role_1' from dual
  7    ),

中 的 每 一 个
查询 从 此处 开始 ：

8  temp as
  9    -- RN will later be used in correlated subqueries to fetch
 10    -- ADDRESS and ZIP values that belong to the same row
 11    (select caseid, name, address, zip, role,
 12       row_number() over (partition by caseid, name order by address) rn
 13     from test
 14    )
 15  -- finally, fetch what you can from the TEMP CTE; use correlated subqueries
 16  -- for the rest and aggregate ROLE values
 17  select a.caseid,
 18    a.name,
 19    --
 20    (select b.address
 21     from temp b
 22     where b.caseid = a.caseid
 23       and b.name = a.name
 24       and b.rn = 1
 25    ) address,
 26    --
 27    (select b.zip
 28     from temp b
 29     where b.caseid = a.caseid
 30       and b.name = a.name
 31       and b.rn = 1
 32    ) zip,
 33    --
 34    listagg(role, ', ') within group (order by role) role
 35  from temp a
 36  group by a.caseid, a.name
 37  /

格式
结果 ：

CASEID NAME ADDRESS   ZIP   ROLE
---------- ---- --------- ----- ---------------
         1 Joe  address_1 zip_1 role_1
         1 Bill address_4 zip_4 role_1, role_2
         1 John address_2 zip_2 role_1
         2 Bob  address_6 zip_6 role_1

SQL>

格式

ST样

select 
   caseid, name, 
   min(address) keep (dense_rank first order by address, zip), 
   min(zip) keep (dense_rank first order by address, zip), 
   listagg(role)
from <crappy table> 
group by caseid, name