oracle wm_concat函数，用于列转行，逗号分隔

wm_concat函数

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用

准备测试数据

SQL> createtabletest(id number,name varchar2(20));

SQL> insert into test values(1,'a');

SQL> insert into test values(1,'b');

SQL> insert into test values(1,'c');

SQL> insert into test values(2,'d');

SQL> insert into test values(2,'e');

SQL> commit;

效果1 : 行转列

SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)

-------------------------------------------------------------------------

a,b,c,d,e

效果2: 把结果里的逗号替换成"|"

SQL> select replace(wm_concat(name),','|') from test;

REPLACE(WM_CONCAT(NAME),'|')

-----------------------------------------------------------------------

a|b|c|d|e

效果3:按ID分组合并name

SQL> select id,wm_concat(name) name from test group by id;

ID NAME

---------- ------------------------------

1 a,c

2 d,e

懒人扩展用法:

案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename",基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单

SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';

'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'

--------------------------------------------------------------------------------

create or replace view as select DEPTNO,DNAME,LOC from dept

Oracle9i添加 wm_concat函数（转）

一：

创建type头
create or replace type string_sum_obj as object (
--聚合函数的实质就是一个对象
sum_string varchar2(4000),
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number,sans-serif; font-size:14px; line-height:25.2px"> --对象初始化
member function ODCIAggregateIterate(self in out string_sum_obj,value in varchar2) return number,sans-serif; font-size:14px; line-height:25.2px"> --聚合函数的迭代方法(这是最重要的方法)
member function ODCIAggregateMerge(self in out string_sum_obj,v_next in string_sum_obj) return number,sans-serif; font-size:14px; line-height:25.2px"> --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合

member function ODCIAggregateTerminate(self in string_sum_obj,return_value out varchar2,v_flags in number) return number
--终止聚集函数的处理,返回聚集函数处理的结果.
)
创建type具体
create or replace type body string_sum_obj is
static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is
begin
v_self := string_sum_obj(null);
return ODCICONST.Success;
end;
/* 连接，解决逗号分隔第一个字母是逗号的问题 */
if not self.sum_string is null then
self.sum_string := self.sum_string ||','|| value;
else
self.sum_string := self.sum_string || value;
end if;
/* 最大值 */
if self.sum_string<value then
self.sum_string:=value;
/* 最小值 */
if self.sum_string>value then
/* 连接 */
self.sum_string := self.sum_string || v_next.sum_string;
if self.sum_string<v_next.sum_string then
self.sum_string:=v_next.sum_string;
if self.sum_string>v_next.sum_string then
return_value:= self.sum_string;
end;
创建函数
create or replace function wm_concat(value Varchar2) return Varchar2
parallel_enable aggregate using string_sum_obj;
二：
先创建这个类型
create or replace type strcat_type as object (
cat_string varchar2(4000),sans-serif; font-size:14px; line-height:25.2px"> static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number,sans-serif; font-size:14px; line-height:25.2px"> member function ODCIAggregateIterate(self In Out strcat_type,sans-serif; font-size:14px; line-height:25.2px"> member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number,sans-serif; font-size:14px; line-height:25.2px"> member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number
缺少类型体内容：
....
然后创建这个函数
CREATE OR REPLACE FUNCTION strcat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING strcat_type;
几个用法：9i: SYS_CONNECT_BY_PATH 函数
SQL> select id,replace(wmsys.wm_concat(rmak),'') from test group by id;
ID REPLACE(WMSYS.WM_CONCAT(RMAK),sans-serif; font-size:14px; line-height:25.2px">---------- --------------------------------------------------------------------------------
9 timggg
21 littlefff
23 tom
SQL> select * from idtable;
ID NAME
---------- ------------------------------
10 ab
10 bc
10 cd
20 hi
20 ij
20 mn
6 rows selected
2 group by id;
10 ab,bc,cd
20 hi,ij,mn
20 ab,cd,hi,wmsys.wm_concat(name) over (order by id,name) name from idtable;

个人觉得这个用法比较有趣.
6 rows selected

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!