文档介绍：1、列转行CREATETABLEt_col_row(IDINT,c1VARCHAR2(10),c2VARCHAR2(10),c3VARCHAR2(10));INSERTINTOt_col_rowVALUES(1,'v11','v21','v31');INSERTINTOt_col_rowVALUES(2,'v12','v22',NULL);INSERTINTOt_col_rowVALUES(3,'v13',NULL,'v33');INSERTINTOt_col_rowVALUES(4,NULL,'v24','v34');INSERTINTOt_col_rowVALUES(5,'v15',NULL,NULL);INSERTINTOt_col_rowVALUES(6,NULL,NULL,'v35');INSERTINTOt_col_rowVALUES(7,NULL,NULL,NULL);COMMIT;SELECT*FROMt_col_row;1)UNIONALL适用范围:8i,9i,10g及以后版本SELECTid,',c1cvFROMt_col_rowUNIONALLSELECTid,',c2cvFROMt_col_rowUNIONALLSELECTid,',c3cvFROMt_col_row;若空行不需要转换,只需加一个where条件,WHERECOLUMNISNOTNULL即可。2)MODEL适用范围:,cvFROMt_col_rowMODELRETURNUPDATEDROWSPARTITIONBY(ID)DIMENSIONBY(0ASn)MEASURES('xx','yyy'AScv,c1,c2,c3)[1]='c1',cn[2]='c2',cn[3]='c3',cv[1]=c1[0],cv[2]=c2[0],cv[3]=c3[0]);3)collection适用范围:8i,9i,10g及以后版本要创建一个对象和一个集合:VARCHAR2(10),cvVARCHAR2(10));CREATETYPEcv_varrASVARRAY(8)OFcv_pair;SELECTid,,,TABLE(cv_varr(cv_pair('c1',),cv_pair('c2',),cv_pair('c3',)))tORDERBY1,2;2、行转列CREATETABLEt_row_colASSELECTid,',c1cvFROMt_col_rowUNIONALLSELECTid,',c2cvFROMt_col_rowUNIONALLSELECTid,',c3cvFROMt_col_row;SELECT*FROMt_row_colORDERBY1,2;1)AGGREGATEFUNCTION适用范围:8i,9i,10g及以后版本SELECTid,MAX(,'c1',cv,NULL))ASc1,MAX(,'c2',cv,NULL))ASc2,MAX(,'c3',cv,NULL))ASc3FROMt_row_colGROUPBYidORDERBY1;MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。被指定的转置列只能有一列,但固定的列可以有多列,请看下面的例子:SELECTmgr,deptno,empno,enameFROMempORDERBY1,2;SELECTmgr,deptno,MAX(decode(empno,'7788',ename,NULL))"7788",MAX(decode(empno,'7902',ename,NULL))"7902",MAX(decode(empno,'7844',ename,NULL))"7844",MAX(decode(empno,'7521',ename,NULL))"7521",MAX(decode(empno,'7900',ename,NULL))"7900",MAX(decode(empno,'7499',ename,NULL))"7499",MAX(decode(empno,'7654',ename,NULL))"7654"FROMempWHEREmgrIN(7566,7698)ANDdeptnoIN(20,30)GROUPBYmgr,deptnoORDERBY1,2;这里转置列为empno,固定列为mgr,deptno。还有一种行转列的方式,就是相同组中的行值变为单个列值,但转置的行值不变为列名:_3CV_31c1v11c2v21c3v312c1v12c2v22c33c1v13c2c3v334c1c2v24c3v345c1v15c2c36c1c2c3v357c1c2c3这种情