selectt.rank,t.Namefromt_menu_itemt;
10CLARK
10KING
10MILLER
20ADAMS
20FORD
20JONES
20SCOTT
20SMITH
30ALLEN
30BLAKE
30JAMES
30MARTIN
30TURNER
30WARD
--------------------------------
我们通过10g所提供的WMSYS.WM_CONCAT函数即可以完成行转列的效果
selectt.rank,WMSYS.WM_CONCAT(t.Name)TIMEFromt_menu_itemtGROUPBYt.rank;
DEPTNOENAME
----------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
例子如下：
SQL>createtableidtable(idnumber,namevarchar2(30));
Tablecreated
SQL>insertintoidtablevalues(10,'ab');
1rowinserted
SQL>insertintoidtablevalues(10,'bc');
1rowinserted
SQL>insertintoidtablevalues(10,'cd');
1rowinserted
SQL>insertintoidtablevalues(20,'hi');
1rowinserted
SQL>insertintoidtablevalues(20,'ij');
1rowinserted
SQL>insertintoidtablevalues(20,'mn');
1rowinserted
SQL>select*fromidtable;
IDNAME
----------------------------------------
10ab
10bc
10cd
20hi
20ij
20mn
6rowsselected
SQL>selectid,wmsys.wm_concat(name)namefromidtable
2groupbyid;
IDNAME
------------------------------------------------------------------------------------------
10ab,bc,cd
20hi,ij,mn
SQL>selectid,wmsys.wm_concat(name)over(orderbyid)namefromidtable;
IDNAME
------------------------------------------------------------------------------------------
10ab,bc,cd
10ab,bc,cd
10ab,bc,cd
20ab,bc,cd,hi,ij,mn
20ab,bc,cd,hi,ij,mn
20ab,bc,cd,hi,ij,mn
6rowsselected
SQL>selectid,wmsys.wm_concat(name)over(orderbyid,name)namefromidtable;
IDNAME
------------------------------------------------------------------------------------------
10ab
10ab,bc
10ab,bc,cd
20ab,bc,cd,hi
20ab,bc,cd,hi,ij
20ab,bc,cd,hi,ij,mn
6rowsselected
个人觉得这个用法比较有趣.
SQL>selectid,wmsys.wm_concat(name)over(partitionbyid)namefromidtable;
IDNAME
------------------------------------------------------------------------------------------
10ab,bc,cd
10ab,bc,cd
10ab,bc,cd
20hi,ij,mn
20hi,ij,mn
20hi,ij,mn
6rowsselected
SQL>selectid,wmsys.wm_concat(name)over(partitionbyid,name)namefromidtable;
IDNAME
------------------------------------------------------------------------------------------
10ab
10bc
10cd
20hi
20ij
20mn
6rowsselected
ps:
wmsys.wm_concat、sys_connect_by_path、自定义行数实现行列转换：
CREATETABLEtab_name(IDINTEGERNOTNULLPRIMARYKEY,cNameVARCHAR2(20));
CREATETABLEtab_name2(IDINTEGERNOTNULL,pNameVARCHAR2(20));
INSERTINTOtab_name(ID,cName)VALUES(1,'百度');
INSERTINTOtab_name(ID,cName)VALUES(2,'Google');
INSERTINTOtab_name(ID,cName)VALUES(3,'网易');
INSERTINTOtab_name2(ID,pName)VALUES(1,'研发部');
INSERTINTOtab_name2(ID,pName)VALUES(1,'市场部');
INSERTINTOtab_name2(ID,pName)VALUES(2,'研发部');
INSERTINTOtab_name2(ID,pName)VALUES(2,'平台架构');
INSERTINTOtab_name2(ID,pName)VALUES(3,'研发部');
COMMIT;
期望结果：
IDcNamepName
1百度研发部,市场部
2Google研发部
3网易研发部,平台架构
方法一:使用wmsys.wm_concat()
SELECTt1.ID,ame,wmsys.wm_concat(t2.pName)FROMtab_namet1,tab_name2t2WHEREt1.ID=ame,t1.id;
方法二:使用sys_connect_by_path
selectid,cName,ltrim(max(sys_connect_by_path(pName,',')),',')from(selectrow_number()over(PARTITIONbyt1.idORDERbycName)r,t1.*,t2.pNamefromtab_namet1,tab_name2t2wheret1.id=t2.id)
startwithr=1CONNECTbypriorr=r-1andpriorid=idgroupbyid,cNameorderbyid;
方法三:使用自定义函数
createorreplacefunctioncoltorow(midIdINT)RETURNVARCHAR2is
ResultVARCHAR2(1000);
begin
FORcurIN(SELECTpNameFROMtab_name2t2WHEREmidId=t2.id)LOOP
RESULT:=RESULT||cur.pName||',';
ENDLOOP;
RESULT:=rtrim(RESULT,',');
return(Result);
endcoltorow;
SELECTt1.*,coltorow(t1.ID)FROMtab_namet1,tab_name2t2WHEREt1.ID=t2.IDGROUPBYt1.ID,ameORDERBYt1.ID;
