如何在Oracle10g中按顺序连接多行顺序 [英] How to concatenate multiple rows order by sequence in Oracle10g

问题描述

如果我有这样的数据:

GROUP  |  SEQUENCE  |  COMMAND
------------------------------
ONE    |     3      |  <message2>MESSAGE</message2>
ONE    |     1      |  <?xml version="1.0" encoding="UTF-8"?>
ONE    |     2      |  <message1>MESSAGE</message1>
TWO    |     2      |  <message2>MESSAGE</message2>
TWO    |     1      |  <?xml version="1.0" encoding="UTF-8"?>
........
TWO    |    10      |  <message9>MESSAGE</message9>

如何将命令连接成这样:

How can I concatenate the command to be like this:

GROUP  |  COMMAND
-----------------
ONE    |  <?xml version="1.0" encoding="UTF-8"?>,<message1>MESSAGE</message1>,<message2>MESSAGE</message2>
TWO    |  <?xml version="1.0" encoding="UTF-8"?>,<message1>MESSAGE</message1>, .. ,<message9>MESSAGE</message9>

我在下面使用了此查询，但根据其序列号，命令列的顺序不正确:

I used this query below but the command column is not in order according to their sequence number:

SELECT GROUP, WM_CONCAT(COMMAND) AS COMMAND
     FROM (SELECT GROUP, SEQUENCE, COMMAND FROM TBL ORDER BY SEQUENCE)
     GROUP BY GROUP

//AND THIS

SELECT GROUP, WM_CONCAT(DISTINCT COMMAND) AS COMMAND
 FROM (SELECT GROUP, SEQUENCE, COMMAND FROM TBL ORDER BY SEQUENCE)
 GROUP BY GROUP

任何建议都将不胜感激. ^ _ ^

Any advice and suggestions will be greatly appreciated. ^_^

推荐答案

请勿使用WM_CONCAT.阅读为什么不使用WM_CONCAT函数甲骨文?

请参阅本主题 https://stackoverflow.com/a/28758117/3989608 .

它没有文档，依赖于WM_CONCAT的任何应用程序一旦升级到12c都将无法工作，因为它已从最新的12c版本中删除.

It is undocumented, and any application which relies on WM_CONCAT will not work once upgraded to 12c because it has been removed from the latest 12c version.

根据数据库版本，有很多方法可以进行字符串聚合.请参见下面的几个示例:

There are many ways of doing string-aggregation, depending on the database version. See few examples below:

11gR2

使用LIASTAGG:

SQL> SELECT grp,
  2    listagg(command, ',') WITHIN GROUP(
  3  ORDER BY seq) command
  4  FROM t
  5  GROUP BY grp;

GRP COMMAND
--- --------------------------------------------------------------------------------------------
ONE <?xml version=1.0 encoding=UTF-8?>,<message1>MESSAGE</message1>,<message2>MESSAGE</message2>
TWO <?xml version=1.0 encoding=UTF-8?>,<message2>MESSAGE</message2>,<message9>MESSAGE</message9>

SQL>

9i及更高版本

使用ROW_NUMBER()和SYS_CONNECT_BY_PATH:

SQL> SELECT grp,
  2         LTRIM(MAX(SYS_CONNECT_BY_PATH(command,','))
  3         KEEP (DENSE_RANK LAST ORDER BY seq),',') command
  4  FROM   (SELECT grp,
  5                 command,
  6                 seq,
  7                 ROW_NUMBER() OVER (PARTITION BY grp ORDER BY seq) AS curr,
  8                 ROW_NUMBER() OVER (PARTITION BY grp ORDER BY seq) -1 AS prev
  9          FROM   t)
 10  GROUP BY grp
 11  CONNECT BY prev = PRIOR curr AND grp = PRIOR grp
 12  START WITH curr = 1;

GRP COMMAND
--- --------------------------------------------------------------------------------------------
ONE <?xml version=1.0 encoding=UTF-8?>,<message1>MESSAGE</message1>,<message2>MESSAGE</message2>
TWO <?xml version=1.0 encoding=UTF-8?>,<message2>MESSAGE</message2>,<message9>MESSAGE</message9>

SQL>

这篇关于如何在Oracle10g中按顺序连接多行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！