Oracle中的列转行实现字段拼接用例

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

文章目录

Oracle中的列转行实现字段拼接

场景

在SQL使用过程中经常有这种需求：将某列字段拼接成in('XX','XX','XX','XX','XX','XX' ...)做为查询条件。

实现

select 'in ('''||replace(wm_concat(EMPNO),',',''',''')||''')' from EMP

总结

两端字符的拼接

这里用'in ('''和''')'分别实现了in ('和')的前后拼接，以''')'为例，其中第一个第四个'是Oracle中的字符串连接符，第二个'是一个转义字符,第三个'才是我们真正被转义的内容。
lpad()、rpad()在两端字符的填充中较难控制填充字符串长度。

wm_concat的使用

这里推荐使用LISTAGG()函数,主要因为：
在不同的oracle版本中，WMSYS.WM_CONTACT返回的数据类型不一致，10G版本中，返回的是字符串类型，11G版本中，返回的是clob类型,Oracle 11gR2和12C上已经摒弃了wm_concat函数。
LISTAGG()支持自定义分割字符串，默认值为 NULL，而WMSYS.WM_CONTACT固定只能用'拼接，除非使用REPLACE()替换为不同拼接符。

select mgr,'in (''' ||REPLACE(LISTAGG(EMPNO, ',') within group(order by mgr), ',', ''','& 大专栏 Oracle中的列转行实现字段拼接用例#39;') || ''')' from EMP group by mgr

扩展

转义符

'（单引号）的转义

Select 'test' || '''' from dual; Select 'test ''' from dual;

ASCII码实现转义

Select 'Tom' || chr(38) || 'Jerry' from dual; --chr(38)是‘&'的ASCII码

ESCAPE转义

使用 ESCAPE关键字定义转义符。在模式中，当转义符置于通配符之前时，该通配符就解释为普通字符。

SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%%'; -- 查找全部 SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%_%'; -- 查找全部 SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/%'; -- 查找含有左斜杠 SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/%%' ESCAPE '/'; -- 使用/转义，查找含有%的字符 SELECT * FROM t_test tt WHERE tt.t_ch LIKE '%/_%' ESCAPE '/'; -- 使用/转义，查找含有_的字符

LISTAGG函数

语法

LISTAGG(aggregate_expression [, 'delimiter' ] ) [ WITHIN GROUP (ORDER BY order_list) ]

参数

aggregate_expression:
提供要聚合的值的任何有效表达式（如列名称）。忽略 NULL 值和空字符串。LISTAGG 不支持 DISTINCT 表达式。
delimiter:
可选。将分隔串联的值的字符串常数。默认值为 NULL。
WITHIN GROUP (ORDER BY order_list):
可选。指定聚合值的排序顺序。