oracle select 转一行,ORACLE 多行转一行函数WMSYS.WM_CONCAT和LISTAGG的使用

WMSYS.WM_CONCAT缺点：

1、WMSYS.WM_CONCAT是非公开函数,它的用户是WMSYS,而不是SYS或者SYSTEM.

2、大量使用WM_CONCAT函数也会导致临时表空间爆满,这是因为返回类型为CLOB,CLOB占用的临时表空间只有在连接释放后才会释放.

3、ORACLE12G后不支持该函数的使用

WMSYS.WM_CONCAT 使用：

SELECT TABLE_NAME,COLUMN_NAME,COLUMN_ID

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TEST_LA';--表名可根据实际情况替换

SELECT TABLE_NAME,WM_CONCAT(COLUMN_NAME)

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TEST_LA'

GROUP BY TABLE_NAME;

SELECT TABLE_NAME,TO_CHAR(WM_CONCAT(COLUMN_NAME))

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TEST_LA'

GROUP BY TABLE_NAME;

WMSYS.WM_CONCAT 在 10.2.0.4版本以前是 VARCHAR2 类型,但是 从 10.2.0.5 开始,是 CLOB 类型

ORACLE12G后可使用LISTAGG函数代替。

Oracle 11g Release 2介绍了LISTAGG 函数，使得聚集连接字符串变得很容易。并且允许使用我们指定连接串中的字段顺序

LISTAGG函数用法：

LISTAGG根据order by子句对列值进行排序，将排序后的结果拼接起来。

measure_expr：可以是任何基于列的表达式。

delimiter：分隔符，默认为NUL

order_by_clause：order by子句决定了列值被拼接的顺序。

通过该用法，可以看出LISTAGG函数不仅可作为一个普通函数使用，也可作为分析函数。

LISTAGG函数使用：

SELECT TABLE_NAME,LISTAGG(COLUMN_NAME, ',') WITHIN GROUP(ORDER BY COLUMN_ID) AS ALL_COLUMN

FROM USER_TAB_COLUMNS

WHERE TABLE_NAME = 'TEST_LA'

GROUP BY TABLE_NAME;

效果同WM_CONCAT函数。