汇总顶点 - 作为字符串

Question

我在Oracle 18C表中存储了多个部分的多线顶点。

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

示例数据：

---

”没有单语引号）：

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

如何使用Oracle SQL进行操作？

Answer 1

使用listAgg函数

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;