Select few rows from a result table and achieve below output

user_14281912 0
Viewed 68

I have two tables like 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

I need to join both tables and get the output table like mentioned below

So I have written a query like below

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

By executing the above query I'm getting below output

Output:

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 want to remove the rows which has employee or object as "i-%" from the above output table which i have got after executing the query and place them in separate column with their respectice "boss" group. And get the output table as below

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

OR

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

Can anyone help me on achieving the output which i desire?

listagg oracle sql
2 Answers

My take would be to use listagg separately for the i-% case,

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 for reference

user_138780230

Another variant:

with joined as (
   select
      boss, 
      employee, 
      object, 
      price,
      case when employee like 'i-%' then 1 else 0 end grp,
      row_number()over(partition by boss, case when employee like 'i-%' then 1 else 0 end order by employee) rn
   from T1 
        left join T2
             on T1.employee=T2.worker 
)
select
  j1.*, j2.employee as person
from joined j1
     left join joined j2
          on  j1.rn   = j2.rn 
          and j1.boss = j2.boss 
          and j2.grp=1
where j1.grp=0
order by 1,2,3;

As you can see, at first I created CTE joined where I put your original join and added a couple of columns: grp - 0 for usual rows, and 1 for 'i-' rows, second column is rn - row_number partitioned by boss and grp and ordered by employee - you can see it in final results as rn column. So we can use boss and rn to join different groups.

Full test case with sample data:

with t1( Boss,employee  ) as (
select 'Bran'  , 'josh'      from dual union all
select 'Bran'  , 'paul'      from dual union all
select 'Bran'  , 'i-sara'    from dual union all
select 'Bran'  , 'shaun'     from dual union all
select 'Joseph', 'will'      from dual union all
select 'Joseph', 'i-alex'    from dual union all
select 'Joseph', 'i-vicky'   from dual union all
select 'Joseph', 'mary'      from dual union all
select 'Joseph', 'cristi'    from dual
),t2(Worker,object,price) as (
select 'josh'   ,   'bus'     , 2    from dual union all
select 'paul'   ,   'car'     , 5    from dual union all
select 'i-sara' ,   'i-sara'  , null from dual union all
select 'shaun'  ,   'skate'   , 3    from dual union all
select 'will'   ,   'football', 2    from dual union all
select 'i-alex' ,   'i-alex'  , null from dual union all
select 'mary'   ,   'dino'    , 6    from dual union all    
select 'i-vicky',   'i-vicky' , null from dual union all
select 'cristi' ,   'bike'    , 5    from dual
)
,joined as (
   select
      boss, 
      employee, 
      object, 
      price,
      case when employee like 'i-%' then 1 else 0 end grp,
      row_number()over(partition by boss, case when employee like 'i-%' then 1 else 0 end order by employee) rn
   from T1 
        left join T2
             on T1.employee=T2.worker 
)
select
  j1.*, j2.employee as person
from joined j1
     left join joined j2
          on  j1.rn   = j2.rn 
          and j1.boss = j2.boss 
          and j2.grp=1
where j1.grp=0
order by 1,2,3;

Results:

BOSS   EMPLOYE OBJECT        PRICE        GRP         RN PERSON
------ ------- -------- ---------- ---------- ---------- -------
Bran   josh    bus               2          0          1 i-sara
Bran   paul    car               5          0          2
Bran   shaun   skate             3          0          3
Joseph cristi  bike              5          0          1 i-alex
Joseph mary    dino              6          0          2 i-vicky
Joseph will    football          2          0          3

6 rows selected.
user_4291000
Related