oracle数据库将一列的值拼接成一行,并且各个值之间用逗号隔开

时间:2022-12-03 09:52:04

使用场景:把某一列值转换为逗号分隔的字符串

例子:比如查询所有的的表空间如下,现在要获得所有的表空间用逗号分隔的字符串(比如rman duplicate的时候skip表空间)

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
CTXD
APPS_TS_TX_DATA
APPS_TS_TX_IDX
OWAPUB
APPS_TS_QUEUES
ODM
OLAP
APPS_TS_TOOLS
APPS_TS_SEED
APPS_TS_INTERFACE
APPS_TS_MEDIA
XXCUS
APPS_UNDOTS1
APPS_TS_SUMMARY
APPS_TS_NOLOGGING
XXAPEX
PORTAL
APPS_TS_ARCHIVE
TEMP1
MES
APPS_QUERY
XXAPS
XXAGILE
XXBI
XXOCS
XXOA

28 rows selected.

解决方案:wmsys.wm_concat()拼接字段 以逗号分开

SQL> select wmsys.wm_concat(name) from  v$tablespace;

WMSYS.WM_CONCAT(NAME)
--------------------------------------------------------------------------------
SYSTEM,SYSAUX,CTXD,APPS_TS_TX_DATA,APPS_TS_TX_IDX,OWAPUB,APPS_TS_QUEUES,ODM,OLAP
,APPS_TS_TOOLS,APPS_TS_SEED,APPS_TS_INTERFACE,APPS_TS_MEDIA,XXCUS,APPS_UNDOTS1,A
PPS_TS_SUMMARY,APPS_TS_NOLOGGING,XXAPEX,PORTAL,APPS_TS_ARCHIVE,TEMP1,MES,APPS_QU
ERY,XXAPS,XXAGILE,XXBI,XXOCS,XXOA