oracle SQL Concatenate 2 columns based on condition

Question

let's say i have a table :

+----+------------+------------+
| ID |   NAME1    |     NAME2  |
+----+------------+------------+
| A1 |    DAVE    |    DAN     |
| A1 |    DOR     |    MON     |
| A2 |    STEPHAN |    ARKEL   |
| A3 |    ASH     |    CATCHAM |
| A4 |    RON     |    DON     |
| A4 |    OFIR    |    DOL     |
| A4 |    OFRA    |    SOL     |
+----+------------+------------+

i want to concatenate based on ID , if the next row has the same id add both names like below :

+----+-----------------------------------+
| ID |             NEW_NAME              |
+----+-----------------------------------+
| A1 |    DAVE~~DAN^^DOR~~MON            |
| A2 |    STEPHAN~~ARKEL                 |
| A3 |    ASH~~CATCHAM                   |
| A4 |    RON~~DON^^OFIR~~DOL^^OFRA~~SOL |
+----+-----------------------------------+

Thanks .

Answer 1

You can do:

select
  id,
  listagg(name1 || '~~' || name2, '^^') 
    within group (order by name1, name2) as new_name
from t
group by id

See running example at SQL<>Fiddle.