通过Oracle串联

Question

这是一个样本表数据

Fruit   Number
Apple    1
Apple    2
Apple    3
Kiwi     6
Kiwi     10

我尝试连接表列值以获取以下内容

Fruit   Number
Apple    1-2-3
Kiwi     6-10

有没有办法查询此或存储过程？像Concatenate over（partition by）之类的东西，我对存储过程了解不多。谢谢！

Answer 1

OP在Oracle 10g上运行 ，而 LISTAGG 在 11g第2版 中引入。

因此，在Oracle版本 11克之前， 那里LISTAGG是不支持，你可以使用 ROW_NUMBER（） 和
SYS_CONNECT_BY_PATH 功能。

SELECT fruit,
  LTRIM(MAX(SYS_CONNECT_BY_PATH(number,','))
  KEEP (DENSE_RANK LAST ORDER BY curr),',') AS fruits_agg
  FROM   (SELECT fruit,
                number,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) AS curr,
                ROW_NUMBER() OVER (PARTITION BY fruit ORDER BY number) -1 AS prev
         FROM   table_name)
  GROUP BY fruit
  CONNECT BY prev = PRIOR curr AND fruit = PRIOR fruit
 START WITH curr = 1;

笔记

切勿使用，WM_CONCAT因为它是未记录的功能，并且已从12c版本中删除。

曾经依赖wm_concat功能的任何应用程序一旦升级到，都将无法工作12c。从那以后，它已被删除。请参阅为什么不在Oracle中使用WM_CONCAT函数？

SQL> select banner from v$version where rownum = 1;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL> SELECT object_name
  2  FROM dba_objects
  3  WHERE owner='WMSYS'
  4  AND object_name LIKE 'WM\_%' ESCAPE '\';

OBJECT_NAME
----------------------------------------------------------------------------
WM_REPLICATION_INFO
WM_RDIFF
WM_PERIOD
WM_PERIOD
WM_OVERLAPS
WM_MEETS
WM_LESSTHAN
WM_LDIFF
WM_INTERSECTION
WM_INSTALLATION
WM_GREATERTHAN
WM_EVENTS_INFO
WM_ERROR
WM_ERROR
WM_EQUALS
WM_DDL_UTIL
WM_DDL_UTIL
WM_CONTAINS
WM_COMPRESS_BATCH_SIZES
WM_COMPRESSIBLE_TABLES

20 rows selected.

SQL>

您将收到`` 无效标识符 ‘’错误：

SQL> SELECT banner FROM v$version;

BANNER
----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE    12.1.0.1.0      Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production

SQL> SELECT deptno, wm_concat(ename) FROM emp;
SELECT deptno, wm_concat(ename) FROM emp
               *
ERROR at line 1:
ORA-00904: "WM_CONCAT": invalid identifier

因此，没有必要依赖 未记录的功能 ，而该 功能 在最新版本中不再可用。