I wonder, how can I record data from different rows on a single line.

If customer has more than one shop in one row, then I want to bring up its appropriate tariff,name and cug, for example id = 1.

If the shop1 = 577 is fixed on the customer, then its corresponding value should be written in the CUG field, for example id = 2.

There is a table:

create table transaction (id, shop1, sho2,shop3,shop_name,tariff,cug) as
 select   1, 789, null, 987, bundle2, 5, 0  from dual union all
 select   1, 789, null, null , bundle1, 4, 0 from dual union all
 select   1, null,null,987, bundle2, 6, 0 from dual union all
 select   2, null,null,987, bundle2, 6, 0 from dual union all
 select   2, null, 635,null, bundle1, 8, 0 from dual union all
 select   2, 577, null, null, 0, 3, cug2 from dual union all
 select   2, null, 635, 987, bundle2, 5, 0 from dual union all
 select   3, null, 852, null, bundle1, 6, 0 dual union all
 select   4, 753, 357, null, bundle2, 1.5, 0 from dual union all
  select   5, 369, 147, 422, bundle1, 4.5, 0 from dual ;

My expected table:

CodePudding user response：

Is this what you want?
Your description is difficult to follow.

create table transaction (id, shop1, shop2,shop3,shop_name,tariff,cug) as
 select   1, 789, null, 987, 'bundle2', 5, '0'  from dual union all
 select   1, 789, null, null , 'bundle1', 4, '0' from dual union all
 select   1, null,null,987, 'bundle2', 6, '0' from dual union all
 select   2, null,null,987, 'bundle2', 6, '0' from dual union all
 select   2, null, 635,null, 'bundle1', 8, '0' from dual union all
 select   2, 577, null, null, '0', 3, 'cug2' from dual union all
 select   2, null, 635, 987, 'bundle2', 5, '0' from dual union all
 select   3, null, 852, null, 'bundle1', 6, '0' from dual union all
 select   4, 753, 357, null, 'bundle2', 1.5, '0' from dual  union all
 select   5, 369, 147, 422, 'bundle1', 4.5, '0' from dual ;

select 
  id,
  max(shop1) shop1,
  max(shop2) shop2,
  max(shop3) shop3,
  /*max(shop_name)*/ shop_name,
  max(tariff) tarif,
  max(cug) cug
from transaction t
join (select id midd,
      max(shop_name) msn
      from transaction
      group by id) m
on id = midd
and shop_name = msn
group by id,shop_name;

ID | SHOP1 | SHOP2 | SHOP3 | SHOP_NAME | TARIF | CUG
-: | ----: | ----: | ----: | :-------- | ----: | :--
 1 |   789 |  null |   987 | bundle2   |     6 | 0  
 2 |  null |   635 |   987 | bundle2   |     6 | 0  
 3 |  null |   852 |  null | bundle1   |     6 | 0  
 4 |   753 |   357 |  null | bundle2   |   1.5 | 0  
 5 |   369 |   147 |   422 | bundle1   |   4.5 | 0  

db<>fiddle here