将顶点行折叠为嵌套表类型（按 ID 聚合）

测试数据：

with cte as (
select 1 as id, 100 as x, 101 as y from dual union all
select 1 as id, 200 as x, 201 as y from dual union all
select 2 as id, 300 as x, 301 as y from dual union all
select 2 as id, 400 as x, 401 as y from dual union all
select 2 as id, 500 as x, 501 as y from dual union all
select 3 as id, 600 as x, 601 as y from dual union all
select 3 as id, 700 as x, 701 as y from dual union all
select 3 as id, 800 as x, 801 as y from dual union all
select 3 as id, 900 as x, 901 as y from dual)

select id, x, y from cte

        ID          X          Y
---------- ---------- ----------
         1        100        101
         1        200        201

         2        300        301
         2        400        401
         2        500        501

         3        600        601
         3        700        701
         3        800        801
         3        900        901

在 SQL 查询中：

我想将顶点折叠到嵌套表中，并按 ID 列聚合。

数据类型为 Oracle Spatial 的 MDSYS.VERTEX_SET_TYPE：

MDSYS.VERTEX_SET_TYPE

该函数返回一个 MDSYS.VERTEX_SET_TYPE 的对象，其中 由 MDSYS.VERTEX_TYPE 对象表组成。甲骨文空间 Graph 将类型 VERTEX_SET_TYPE 定义为：

CREATE TYPE vertex_set_type as TABLE OF vertex_type;

Oracle Spatial and Graph 将对象类型 VERTEX_TYPE 定义为：

CREATE TYPE vertex_type AS OBJECT    
(x   NUMBER,
 y   NUMBER,
 z   NUMBER,
 w   NUMBER,
 v5  NUMBER,
 v6  NUMBER,
 v7  NUMBER,
 v8  NUMBER,
 v9  NUMBER,
 v10 NUMBER,
 v11 NUMBER,
 id  NUMBER);  --The vertex ID attribute is down here.

我认为结果看起来像这样：（三组顶点）

VERTICES
---------------------
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])

--I mocked up that format using this dummy query:  
--select  sdo_util.getvertices(sdo_geometry('linestring(100 101, 200 201)')) from dual

问题：

有没有办法将顶点行折叠到 VERTEX_SET_TYPE 表类型中 - 由 ID 列聚合？

Use the COLLECT聚合函数，然后CAST it to MDSYS.VERTEX_SET_TYPE:

WITH cte (id, x, y) as (
  SELECT 1, 100, 101 FROM DUAL UNION ALL
  SELECT 1, 200, 201 FROM DUAL UNION ALL
  SELECT 2, 300, 301 FROM DUAL UNION ALL
  SELECT 2, 400, 401 FROM DUAL UNION ALL
  SELECT 2, 500, 501 FROM DUAL UNION ALL
  SELECT 3, 600, 601 FROM DUAL UNION ALL
  SELECT 3, 700, 701 FROM DUAL UNION ALL
  SELECT 3, 800, 801 FROM DUAL UNION ALL
  SELECT 3, 900, 901 FROM DUAL
)
SELECT id,
       CAST(
         COLLECT(
           MDSYS.VERTEX_TYPE(x, y, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, id)
           ORDER BY x, y
         )
         AS MDSYS.VERTEX_SET_TYPE
       )
FROM   cte
GROUP BY id

数据库小提琴here