189 8069 5689
本文记录一下Oracle列转行函数在Oracle11的一些不兼容问题，vm_concat在一些业务场景是必须的。不过这个函数使用要谨慎，底层实现应该也是group by等等实现的，性能并不是特别好。这个函数在Oracle12C是没有的，在Oracle11是不太兼容的，Oracle10可以正常使用。最近开发场景遇到这个问题，可以写到了自定义列转行函数的办法去解决。但是这种办法并不一定适用所有的业务场景。
建网站原本是网站策划师、网络程序员、网页设计师等，应用各种网络程序开发技术和网页设计技术配合操作的协同工作。成都创新互联专业提供网站设计制作、成都网站建设,网页设计,网站制作(企业站、响应式网站开发、电商门户网站)等服务,从网站深度策划、搜索引擎友好度优化到用户体验的提升,我们力求做到极致!
解决方案
通过自定义函数解决也是可以的，不过我并不是这样做的。 下面介绍一下我的解决方法。首先分析一下，Oracle19C不兼容vm_concat列转行函数，并不代表其它函数不兼容，或许可以找到其它代替的，通过找资料，发现了Oracle11提供的另外一个函数：listagg()函数 语法：listagg(参数，‘分隔符’) within group(order by 参数id)，验证可以实现在19C正常使用
例如：
to_char(wm_concat(t.busi_id))
可以改写成：
to_char(listagg(t.busi_id,',' ) within GROUP (order by (t.busi_id)))
SQL如:
SELECT listagg(t.busi_id, ',') within GROUP(order by (t.busi_id))
FROM sys_cdc_sync_record t
WHERE cdc_sql_type = 'INSERT_SELECT'
固定列数的行列转换如
student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
语句如下：
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student
2、不定列行列转换如
c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
......
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
--用于返回值
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
SELECT REGEXP_SUBSTR('aa,bbb,cccc', '[^,]+', 1, rownum)
from dual
connect by level = LENGTH(regexp_replace('aa,bbb,cccc', '[^,]+', ''));
with baseData as(
select '1' id,'aaa' name from dual
union all
select '2' ,'bbb' from dual
)
SELECT listagg(name ,',') WITHIN GROUP (ORDER BY id)
FROM baseData ;