更改 Oracle 11gR2 的 WM_CONCAT 函数的分隔符

2021-12-30 00:00:00 function sql oracle11g oracle

通常，WM_CONCAT 是一个聚合函数，它从像这里这样用逗号分隔的表中返回值.

Normally, WM_CONCAT is an aggregate function that return values from table separated by comma like here.

假设我有一个像这样的表 foo:

Suppose I have a table foo like this:

col_id     | col_text


111        | This

111        | is

111        | a

111        | test.

如果我使用这个查询:

SELECT CAST(WM_CONCAT(col_text) AS VARCHAR2(100)), col_id FROM foo

结果是

This, is, a, test.

是否可以将 '.' 或 '|' 的分隔符 (',') 更改为其他字符?code>WM_CONCAT() 函数?

Is it possible to change the separator(',') to other characters like '.' or '|' of the WM_CONCAT() function?

或者创建一个可以像WM_CONCAT()那样执行的用户定义函数?

Or create a user defined function that can be executed like WM_CONCAT()?

推荐答案

您可能想要使用 LISTAGG.

You might want to use LISTAGG.

SELECT col_id, 
       LISTAGG(col_text, '|') WITHIN GROUP (ORDER BY col_text) text
  FROM table1
 GROUP BY col_id

输出:

| COL_ID |            TEXT |
----------------------------
|    111 | This|a|is|test. |

SQLFiddle

UPDATE 如果您需要在列表中获取不同的文本值

UPDATE If you need to get distinct text values in a list 

SELECT col_id, 
       LISTAGG(col_text, '|')
         WITHIN GROUP (ORDER BY col_text) text
  FROM 
(
  SELECT DISTINCT col_id, col_text
    FROM table1
)
 GROUP BY col_id

SQLFiddle

相关文章