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

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

准备测试数据

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 @R_156_3772@ 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;
Helvetica,value in varchar2) return number is
/* 连接，解决逗号分隔第一个字母是逗号的问题 */
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
Helvetica,v_next in String_sum_obj) return number is
/* 连接 */
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
Helvetica,v_flags in number) return number is
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
Helvetica,wmsys.wm_concat(Name) name from IDtable
2 group by ID;
10 ab,bc,cd
20 hi,ij,mn
Helvetica,wmsys.wm_concat(Name) over (order by ID) name from IDtable;
20 ab,cd,hi,wmsys.wm_concat(Name) over (order by ID,Name) name from IDtable;
Helvetica,bc
Helvetica,hi
Helvetica,ij
个人觉得这个用法比较有趣.
Helvetica,wmsys.wm_concat(Name) over (partition by ID) name from IDtable;
6 rows SELEcted