Aggregate vertices-as-rows to string

Question

I have multi-part polyline vertices stored as individual rows in an Oracle 18c table.

ASSET_ID     PART_NUM VERTEX_NUM          X          Y          M
---------- ---------- ---------- ---------- ---------- ----------
001                 1          1          0          5          0
001                 1          2         10         10      11.18
001                 1          3         30          0      33.54
001                 2          1         50         10      33.54
001                 2          2         60         10      43.54

Sample data: db<>fiddle

---

I want to aggregate the rows into a single text value (without the single quotes):

'MULTILINESTRING ((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))'

How can I do that using Oracle SQL?

Answer 1

Use Listagg function

with cte as (
select 001 c1,1 c2,1 c3,0 c4,5 c5,0 c6 from dual union all
select 001,1,2,10,10,11.18 from dual union all
select 001,1,3,30,0,33.54 from dual union all
select 001,2,1,50,10,33.54 from dual union all
select 001,2,2,60,10,43.54 from dual
), cte1 as
(
select c2,listagg(c4||' '||c5||' '||c6,',')  
s1 from cte group by c2 order by c2 asc)
select 'MULTILINESTRING ('||listagg('('||s1||')',',') 
within group (order by c2) ||')' as result from cte1;

fiddle