更改Oracle 11gR2的WM_CONCAT功能的分隔符 [英] Change separator of WM_CONCAT function of Oracle 11gR2

问题描述

假设我有一个表 foo

code>像这样：

  col_id | col_text 
 
 
 111 |这
 
 111 |是
 
 111 | a 
 
 111 |测试。 
  

如果我使用这个查询：

 SELECT CAST（WM_CONCAT（col_text）AS VARCHAR2（100）），col_id FROM foo 
  

结果是

 这是一个测试。 
  

是否可以更改分隔符（'，''。'或'|' WM_CONCAT （）函数？

或者创建一个用户定义的函数，可以像 WM_CONCAT（）

解决方案

您可能想要使用 LISTAGG 。

  SELECT col_id，
 LISTAGG（col_text，'|'）WITHIN GROUP（ORDER BY col_text）text 
 FROM table1 
 GROUP BY col_id $ 
 
 
 
 $ b 
输出： C $ C> | COL_ID | TEXT | 
 ---------------------------- 
 | 111 |这| A |是|测试。 | 
  

SQLFiddle

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

  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

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

Suppose I have a table foo like this:

col_id     | col_text


111        | This

111        | is

111        | a

111        | test.

If I use this query:

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

the result would be

This, is, a, test.

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

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

解决方案

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

Output:

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

SQLFiddle

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

这篇关于更改Oracle 11gR2的WM_CONCAT功能的分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！