Oracle SQL - Sum numbers if value is duplicated in concatinated string

Question

I have a SELECT statement which stores values as a concatinated string:
I need such a query, because for each table1.id only one line should exist, although it is possible, that it available in table2 several times. That is very important, therefore I wrote it with LISTAGG.

The query which has been created to get the output:

  SELECT table1.id,
         RTRIM (
             REGEXP_REPLACE (
                 LISTAGG (
                     CASE
                         WHEN table2.unit_id = '-1' THEN NULL
                         ELSE TO_CHAR (table2.unit_idunit_id)
                     END,
                     ',')
                 WITHIN GROUP (ORDER BY table2.unit_idunit_id),
                 '([^,]+)(,|\1|)+',
                 '\1|'),
             '|')
             AS source_load_unit_id,
         RTRIM (
             REGEXP_REPLACE (
                 LISTAGG (
                     CASE
                         WHEN table2.unit_lp = 'NONE' THEN NULL
                         ELSE TO_CHAR (table2.unit_lp)
                     END,
                     ',')
                 WITHIN GROUP (ORDER BY table2.unit_lp),
                 '([^,]+)(,|\1|)+',
                 '\1|'),
             '|')
             AS source_load_unit_number,
         RTRIM (
             REGEXP_REPLACE (
                 LISTAGG (
                     CASE
                         WHEN table2.target_unit_id = '-1' THEN NULL
                         ELSE TO_CHAR (table2.target_unit_id)
                     END,
                     ',')
                 WITHIN GROUP (ORDER BY
                                   table2.target_unit_id,
                                   '([^,]+)(,|\1|)+',
                                   '\1|'),
                 '|'))
             AS target_load_unit_id,
         RTRIM (
             REGEXP_REPLACE (
                 LISTAGG (
                     CASE
                         WHEN table2.target_unit_lp = 'NONE'
                         THEN
                             NULL
                         ELSE
                             TO_CHAR (table2.target_unit_lp)
                     END,
                     ',')
                 WITHIN GROUP (ORDER BY table2.target_unit_lp),
                 '([^,]+)(,|\1|)+',
                 '\1|'),
             '|')
             AS target_load_unit_number,
         LISTAGG (
             CASE
                 WHEN table2.target_unit_id LIKE '-1'
                 THEN
                     NULL
                 ELSE
                        table2.target_unit_id
                     || '='
                     || TO_CHAR (table2.QUANTITY - table2.shortpick)
             END,
             '|')
         WITHIN GROUP (ORDER BY table2.target_unit_id)
             AS load_unit_qty,
         REPLACE (
             TRIM (
                 BOTH '|' FROM
                     REGEXP_REPLACE (
                         LISTAGG ('|' || table2.user_name || '|',
                                  NULL)
                         WITHIN GROUP (ORDER BY table2.user_name),
                         '(\|[^|]+\|)(\1)+',
                         '\1')),
             '||',
             '|')
             AS pick_user
    FROM my_table1 table1
         JOIN mytable2 table2 ON table2.test1_id = table1.id
GROUP BY table1.id

The output of the query for the column load_unit_qty is like this:
It is possible that one line has concatinated the values duplicated.

|load_unit_qty          |
|-----------------------|
|Test1=1|Test2=1|Test1=1|
|Test3=2                |
|Test4=4|Test4=2|Test5=3|

What I want to have as output for the load_unit_qty is like this:
I want to count the numbers behind '=' and show it only once in the line.

|load_unit_qty          |
|-----------------------|
|Test1=2|Test2=1        |
|Test3=2                |
|Test4=6|Test5=3        |

How can I change the query so that the desired output is shown? Is it possible? Thank you.

Answer 1

Aggregate the results in an inline view of table2:

FROM my_table1 table1
     INNER JOIN (
       SELECT test1_id,
              user_name,
              target_unit_lp,
              target_unit_id,
              unit_idunit_id,
              SUM(quantity - short_pick) OVER (PARTITION BY test1_id, target_unit_id)
                AS total,
              ROW_NUMBER() OVER (PARTITION BY test1_id, target_unit_id ORDER BY ROWNUM)
                AS total_rn
       FROM   mytable2
     ) table2 ON table2.test1_id = table1.id

Then when you want to aggregate in the outer query you can check if it is the first occurrence and ignore any subsequent rows:

LISTAGG(
  CASE
  WHEN table2.target_unit_id LIKE '-1'
  OR   table2.total_rn > 1
  THEN NULL
  ELSE table2.target_unit_id || '=' || table2.total
  END,
  '|'
) WITHIN GROUP (ORDER BY table2.target_unit_id)