sql - 从结果表中选择几行并获得以下输出

Question

我有两个表T1

 Boss     employee  
 Bran     josh 
 Bran     paul
 Bran     i-sara
 Bran     shaun
Joseph    will
Joseph    i-alex
Joseph    i-vicky
Joseph    mary
Joseph    cristi

T2

Worker    object       price
josh      bus          2
paul      car          5
i-sara    i-sara       null
Shaun     skate        3
will      football     2
i-alex    i-alex       null
mary      dino         6        
i-vicky   i-vicky      null
Cristi    bike         5

我需要加入两个表并获得如下所述的输出表

所以我写了如下查询

select boss, employee, object, price from T1 left join on T1.employee=T2.worker order by boss;

通过执行上面的查询，我得到了以下输出

输出:

Boss     employee    object    price
Bran      josh        bus       2
Bran      paul        car       5
Bran      i-sara      i-sara    null
Bran      shaun       skate     3
Joseph    will        football  2
Joseph    i-alex      i-alex    null
Joseph    mary        dino      6
Joseph    i-vicky    i-vicky    null
Joseph    cristi      bike      5

我想从执行查询后得到的上述输出表中删除员工或对象为“i-%”的行，并将它们与各自的“老板”组放在单独的列中。得到如下输出表

Boss     employee  object    price  person
Bran      josh      bus       2    i-sara
Bran      paul      car       5    null
Bran      shaun     skate     3    null
Joseph    will      football  2    i-alex, i-vicky
Joseph    mary      dino      6    null
Joseph    cristi    bike      5    null

或

Boss     employee  object    price  person
Bran      josh      bus       2    i-sara
Bran      paul      car       5    null
Bran      shaun     skate     3    null
Joseph    will      football  2    i-alex
Joseph    mary      dino      6    i-vicky
Joseph    cristi    bike      5    null

谁能帮我实现我想要的输出？

Answer 1

我的做法是对 i-% 情况单独使用 listagg，

select boss, employee, object, price
      ,case when rn = 1 then 
         (select listagg(t2i.worker,',') within group (order by t2i.worker)
            from t2 t2i
            join t1 t1i 
              on t1i.employee = t2i.worker
             and t1i.boss = t1.boss
           where t2i.worker like 'i-%'
          group by t1i.boss
         ) 
       end persons
from
(select boss, employee, object, price,
   row_number() over (partition by t1.boss order by null) rn
  from t1 
left join t2
  on t1.employee = t2.worker
where t1.employee not like 'i-%'
) t1
order by boss;

Db<>fiddle供引用