Oracle的常用内置系统函数

1、wm_concat:连接函数

表temp有两个字段如下：

ID     NAME
---------- -----

10 ab
10 bc
10 cd
 20 hi
 20 ij
20 mn

select id,wm_concat(name) as name from temp;

ID NAME
-------------------------

10 ab,bc,cd
 20 hi,ij,mn

2  sys_connect_by_path:

ps：

wmsys.wm_concat、sys_connect_by_path、自定义行数实现行列转换：

CREATE TABLE tab_name(ID INTEGERNOT NULL PRIMARY KEY,cName VARCHAR2(20));
CREATE TABLE tab_name2(ID INTEGER NOT NULL,pNameVARCHAR2(20));

INSERT INTO tab_name(ID,cName)VALUES (1,'百度');
INSERT INTO tab_name(ID,cName) VALUES (2,'Google');
INSERT INTO tab_name(ID,cName) VALUES (3,'网易');
INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'研发部');
INSERT INTO tab_name2(ID,pName) VALUES (2,'平台架构');
INSERT INTO tab_name2(ID,pName) VALUES (3,'研发部');
COMMIT;

期望结果：

ID            cName                       pName               

1                百度                    研发部,市场部

2                Google                研发部

3                 网易                    研发部,平台架构

方法一:使用wmsys.wm_concat()

SELECTt1.ID,t1.cName,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2t2 WHERE t1.ID=t2.ID GROUP BY t1.cName,t1.id;

方法二:使用sys_connect_by_path

select id, cName,ltrim(max(sys_connect_by_path(pName, ',')), ',') from (selectrow_number() over(PARTITION by t1.id ORDER by cName) r,t1.*,t2.pName from tab_name t1, tab_name2 t2 where t1.id = t2.id)
start with r=1 CONNECT by prior r =r-1 and prior id = id group byid ,cName order by id;

方法三:使用自定义函数

create or replace functioncoltorow(midId INT) RETURN VARCHAR2 is
Result VARCHAR2(1000);
begin
FOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId=t2.id)LOOP
RESULT:=RESULT||cur.pName||',';
END LOOP;
RESULT:=rtrim(RESULT,',');
return(Result);
end coltorow;

SELECT t1.*,coltorow(t1.ID) FROMtab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.ID,t1.cnameORDER BY t1.ID;