Oracle database Merge Row Records, Wmsys. The use of Wm_concat functions

SQL code

Select  from T_menu_item t;

Ten CLARK
Ten KING
Ten MILLER
ADAMS
FORD
JONES
SCOTT
SMITH
ALLEN
BLAKE
JAMES
MARTIN
TURNER
WARD

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

We pass the Wmsys provided by 10g. Wm_concat function to complete row-to-column effects

SQL code

Select  from GROUP  by T.rank;

DEPTNO ename
------ ----------
CLARK, KING, MILLER
ADAMS, FORD, JONES, SCOTT, SMITH
ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

Examples are as follows:

Sql> Create TableIDTable (ID Number, namevarchar2( -));TableCreatedsql> Insert  intoIDTableValues(Ten,'AB');1Row Insertedsql> Insert  intoIDTableValues(Ten,'BC');1Row Insertedsql> Insert  intoIDTableValues(Ten,'CD');1Row Insertedsql> Insert  intoIDTableValues( -,'Hi');1Row Insertedsql> Insert  intoIDTableValues( -,'IJ');1Row Insertedsql> Insert  intoIDTableValues( -,'mn');1Row Insertedsql> Select *  fromidtable;

ID NAME
---------- ------------------------------
Ten AB
Ten BC
Ten CDs
Hi
ij
Mn

6 Rows selected

SQL>Selectfrom2Group by ID;

ID NAME
---------- --------------------------------------------------------------------------------
Ten AB,BC,CD
Hi,ij,mn

SQL>Select over (order by from IDTable;

ID NAME
---------- --------------------------------------------------------------------------------
Ten AB,BC,CD
Ten AB,BC,CD
Ten AB,BC,CD
Ab,bc,cd,hi,ij,mn
Ab,bc,cd,hi,ij,mn
Ab,bc,cd,hi,ij,mn

6 Rows selected

SQL>Select over (order by from IDTable;

ID NAME
---------- --------------------------------------------------------------------------------
Ten AB
Ten AB,BC
Ten AB,BC,CD
Ab,bc,cd,hi
Ab,bc,cd,hi,ij
Ab,bc,cd,hi,ij,mn

6 Rows selected

Personally, this usage is more interesting.

SQL>Select over by from IDTable;

ID NAME
---------- --------------------------------------------------------------------------------
Ten AB,BC,CD
Ten AB,BC,CD
Ten AB,BC,CD
Hi,ij,mn
Hi,ij,mn
Hi,ij,mn

6 Rows selected

SQL>Select over by from IDTable;

ID NAME
---------- --------------------------------------------------------------------------------
Ten AB
Ten BC
Ten CDs
Hi
ij
Mn

6 Rows selected

Ps:

Wmsys.wm_concat, Sys_connect_by_path, custom line count to implement row and column conversions:

CREATE TABLETab_name (IDINTEGER  not NULL PRIMARY KEY, CNameVARCHAR2( -));CREATE TABLETab_name2 (IDINTEGER  not NULL, PNameVARCHAR2( -));INSERT  intoTab_name (Id,cname)VALUES(1,'Baidu');INSERT  intoTab_name (Id,cname)VALUES(2,'Google');INSERT  intoTab_name (Id,cname)VALUES(3,'NetEase');INSERT  intoTab_name2 (Id,pname)VALUES(1,'Research Department');INSERT  intoTab_name2 (Id,pname)VALUES(1,'Marketing Department');INSERT  intoTab_name2 (Id,pname)VALUES(2,'Research Department');INSERT  intoTab_name2 (Id,pname)VALUES(2,'Platform Architecture');INSERT  intoTab_name2 (Id,pname)VALUES(3,'Research Department');COMMIT;

Expected Result:

ID CName PName

100 degree research and Development Department, marketing department

2 Google Research and Development Department

3 NetEase Research and Development Department, platform architecture

Method One: Use Wmsys.wm_concat ()

SELECT  from WHERE t1.id=GROUP by T1.cname,t1.id;

Method Two: Use Sys_connect_by_path

SelectID, CName,LTrim(Max(Sys_connect_by_path (PName,',')),',') from(SelectRow_number () Over(PARTITION byT1.idORDER  byCName) r,t1.*, T2.pname fromTab_name T1, Tab_name2 T2whereT1.id=t2.id) Start withR=1CONNECT byPrior R=R-1  andPrior ID=IdGroup  byID, CNameOrder  byId

Method Three: Use the Custom function

Create or Replace functionColtorow (MididINT)RETURN VARCHAR2  isResultVARCHAR2( +);begin forCurinch(SELECTPName fromTab_name2 T2WHEREMidid=t2.id) Loopresult:=RESULT||Cur.pname||',';ENDLOOP; RESULT:=RTrim(RESULT,',');return(Result);EndColtorow;SELECTT1.*, Coltorow (t1.id) fromTab_name t1,tab_name2 T2WHERET1.id=T2.idGROUP  byT1.id,t1.cnameORDER  byT1.id;

This article transferred from: http://l6259710.iteye.com/blog/1271915

Oracle database Merge Row Records, Wmsys. The use of Wm_concat functions