sql - Splitting strings and aggregating data into existing rows or new rows

Question

I have to query a SQL table that looks something like:

name(varchar)         | amount(double)
_________________________________________
mark, tom, & phil     |  200.
mark                  |  100.
phil                  |  50.

and I need to transform it into this:

name(varchar)         | amount(double)
_________________________________________
mark                  |  300.
phil                  |  250.
tom                   |  200.

Where I am adding the combination amount between three people to any entries already for a person in the table, and if they are not in the table, create a new entry for the person with the combination value.

I do not own the original data source table, so cannot change the design of it.

I tried searching for a similar case here on stack overflow, but could not find something like this.

question from:https://stackoverflow.com/questions/65892207/splitting-strings-and-aggregating-data-into-existing-rows-or-new-rows

Answer 1

Comments explained it; see if such a query helps.

SQL> set define off;
SQL> with
  2  test (name, amount) as
  3    -- sample data
  4    (select 'mark, tom, & phil', 200 from dual union all
  5     select 'mark'             , 100 from dual union all
  6     select 'phil'             ,  50 from dual
  7    ),
  8  temp as
  9    -- split NAME to rows
 10    (select regexp_substr(name, 'w+', 1, column_value) name, amount
 11     from test cross join
 12          table(cast(multiset(select level from dual
 13                              connect by level <= regexp_count(name, ',') + 1
 14                             ) as sys.odcinumberlist))
 15    )
 16  select name, sum(amount)
 17  from temp
 18  group by name;

NAME       SUM(AMOUNT)
---------- -----------
tom                200
phil               250
mark               300

SQL>