Oracle行列轉(zhuǎn)換總結(jié)

1.下10gA.model式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).ALL:8i,9i,10gSELECTid,'c1'cn,cvFROMt_col_rowUNIONALLSELECTid,'c2'cn,cvFROMt_col_rowUNIONALLSELECTid,'c3'cn,cvFROMt_col_row;whereWHERECOLUMNNULL2).MODEL10gSELECTid,cn,FROMt_col_rowMODELRETURNUPDATEDPARTITIONBY(ID)DIMENSIONBY(0ASn)MEASURES('xx'cn,'yyy'cv,c1,c2,c3)RULESUPSERTALL(cn[1]'c1',cn[2]'c2',cn[3]'c3',cv[1]=c1[0],cv[2]=c2[0],cv[3]=c3[0])ORDERBYID,cn;3).collection:8i,9i,10gCREATETYPEcv_pairOBJECT(cnVARCHAR2(10),cvVARCHAR2(10));CREATETYPEcv_varrVARRAY(8)cv_pair;SELECTid,AScn,t.cvFROMt_col_row,TABLE(cv_varr(cv_pair('c1',t_col_row.c1),cv_pair('c2',t_col_row.c2),cv_pair('c3',t_col_row.c3)))tORDERBY1,2;2行列CREATETABLE_colSELECTid,'c1'cn,c1cvFROMUNIONALLSELECTid,'c2'cn,c2cvFROMUNIONALLSELECTid,'c3'cn,c3cvFROMt_col_row;SELECT*FROM_colORDERBY1,2;1).AGGREGATEFUNCTION:8i,9i,10gSELECTid,MAX(decode(cn,'c1',cv,NULL))ASc1,MAX(decode(cn,'c2',cv,NULL))ASc2,MAX(decode(cn,'c3',cv,NULL))ASc3FROMt_row_colGROUPidORDER1;、avg00、-1signdecodeselectdecode(sign(2),-1,段4)fromdual;SELECTmgr,deptno,empno,enameFROMscott.empORDERBY1,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"FROMscott.empWHEREIN(7566,7698)deptno(20,30)GROUPBYmgr,deptnoORDERBY1,2;empnodeptnoCN_1CV_1CN_2CV_2CN_3CV_31c1v11c2v312c1v12c23c1v13c2v334c1c2v24v345c1c26c1v357c1SELECTid,MAX(decode(rn,1,cn,NULL))cn_1,MAX(decode(rn,1,cv,NULL))cv_1,MAX(decode(rn,2,cn,NULL))cn_2,MAX(decode(rn,2,cv,NULL))cv_2,MAX(decode(rn,3,cn,NULL))cn_3,MAX(decode(rn,3,cv,NULL))cv_3FROM(SELECTid,cn,cv,row_number()over(PARTITIONBYidORDERBYcn,cv)FROMt_row_col)GROUPBYID;2).PL/SQL:8i,9i,10gp_rows_column_realp_rows_columnCREATEORREPLACEPACKAGEpkg_dynamic_rows_columnTYPErefcREFCURSOR;PROCEDUREp_print_sql(p_txtVARCHAR2);FUNCTIONf_split_str(p_strVARCHAR2,p_divisionVARCHAR2,p_seqINT)RETURNVARCHAR2;PROCEDUREp_rows_column(p_tableVARCHAR2,p_keep_colsVARCHAR2,p_pivot_colsINVARCHAR2,p_whereVARCHAR2DEFAULTNULL,p_refcINOUTrefc);PROCEDUREp_rows_column_real(p_tableVARCHAR2,p_keep_colsINVARCHAR2,p_pivot_colVARCHAR2,p_pivot_valVARCHAR2,p_whereINVARCHAR2DEFAULTNULL,p_refcINOUTrefc);END;/CREATEORREPLACEPACKAGEBODYpkg_dynamic_rows_columnPROCEDUREp_print_sql(p_txtVARCHAR2)ISv_lenINT;BEGINv_len:=length(p_txt);iIN..v_len/250+dbms_output.put_line(substrb(p_txt,(i-1)*250+1,250));LOOP;END;FUNCTIONf_split_str(p_strVARCHAR2,p_divisionVARCHAR2,p_seqINT)RETURNVARCHAR2ISv_firstINT;v_lastINT;BEGINIFp_seq<1THENRETURNNULL;ENDIF;IFp_seq=1THENIFinstr(p_str,p_division,1,p_seq)=THENRETURNp_str;ELSERETURNsubstr(p_str,1,instr(p_str,p_division,1)-1);ENDIF;ELSEv_firstinstr(p_str,p_division,1,p_seq-1);v_last:=instr(p_str,p_division,1,p_seq);IF(v_last0)IF(v_first>RETURNsubstr(p_str,v_first1);ELSERETURNNULL;ENDIF;ELSERETURNsubstr(p_str,v_first1,v_last-v_first-1);ENDIF;ENDIF;ENDf_split_str;PROCEDUREs_column(p_tableINVARCHAR2,p_keep_colsVARCHAR2,p_pivot_colsINVARCHAR2,hereVARCHAR2DEFAULTNULL,p_refcINOUTrefc)ISv_sqlVARCHAR2(4000);TYPEv_keep_ind_byISTABLEVARCHAR2(4000)INDEXBINARY_INTEGER;v_keepv_keep_ind_by;TYPEv_pivot_ind_byTABLEVARCHAR2(4000)INDEXBINARY_INTEGER;v_pivotv_pivot_ind_by;v_keep_cntINT;v_pivot_cntINT;v_max_colsINT;v_partitionVARCHAR2(4000);v_partition1VARCHAR2(4000);v_partition2VARCHAR2(4000);BEGINv_keep_cnt:=length(p_keep_cols)-length(REPLACE(p_keep_cols,','))1;v_pivot_cnt:=length(p_pivot_cols)-length(REPLACE(p_pivot_cols,','))+1;i1..v_keep_cntv_keep(i):=f_split_str(p_keep_cols,',',i);LOOP;jIN1..v_pivot_cntv_pivot(j)f_split_str(p_pivot_cols,',',j);LOOP;v_sql'selectmax(count(*))from'||p_table||groupby';iIN..v_keep.LASTv_sql:=v_sql||v_keep(i)||',';LOOP;v_sqlrtrim(v_sql,',');EXECUTEIMMEDIATEv_sqlINTOv_max_cols;v_partition:='select';x1..v_keep.COUNTv_partition1:=v_partition1||v_keep(x)||',';LOOP;yIN..v_pivot.COUNTv_partition2:=v_partition2||v_pivot(y)||LOOP;v_partition1:=rtrim(v_partition1,',');v_partition2:=rtrim(v_partition2,',');v_partitionv_partition||v_partition1||||v_partition2||',row_number()over(partitionby'||v_partition1||'orderby'||v_partition2||rnfrom'||p_table;v_partitionrtrim(v_partition,',');v_sql:='select';iIN..v_keep.COUNTv_sql:=v_sql||v_keep(i)||',';LOOP;iIN1..v_max_colsjIN..v_pivot.COUNTv_sqlv_sql||max(decode(rn,'||||||v_pivot(j)||',null))'||v_pivot(j)||||||LOOP;LOOP;IFp_whereISNOTTHENv_sqlrtrim(v_sql,',')||from('||v_partition||'||p_where||')groupby';ELSEv_sql:=rtrim(v_sql,',')||from('||v_partition||')group';ENDIF;iIN..v_keep.COUNTv_sql:=v_sql||v_keep(i)||',';LOOP;v_sqlrtrim(v_sql,',');p_print_sql(v_sql);p_refcv_sql;EXCEPTIONOTHERSTHENp_refcSELECT'x'FROMdualWHERE1;END;PROCEDUREp_rows_column_real(p_tableVARCHAR2,p_keep_colsINVARCHAR2,p_pivot_colVARCHAR2,p_pivot_valVARCHAR2,p_whereINVARCHAR2DEFAULTNULL,p_refcINOUTrefc)v_sqlVARCHAR2(4000);TYPEv_keep_ind_byISTABLEVARCHAR2(4000)INDEXBINARY_INTEGER;v_keepv_keep_ind_by;TYPEv_pivot_ind_byISTABLEVARCHAR2(4000)INDEXBYBINARY_INTEGER;v_pivotv_pivot_ind_by;v_keep_cntINT;v_group_byVARCHAR2(2000);BEGINv_keep_cnt:=length(p_keep_cols)-length(REPLACE(p_keep_cols,','))+1;i1..v_keep_cntv_keep(i):=f_split_str(p_keep_cols,',',i);LOOP;v_sql:='select'||'cast('||p_pivot_col||'asvarchar2(200))as'||p_pivot_col||from'||p_table||'groupby'||p_pivot_col;EXECUTEIMMEDIATEv_sqlBULKCOLLECTINTOv_pivot;iIN..v_keep.COUNTv_group_byv_group_by||v_keep(i)||',';LOOP;v_group_by:=rtrim(v_group_by,',');v_sql'select'||v_group_by||',';x1..v_pivot.COUNTv_sql:=v_sql||max(decode('||p_pivot_col||','||chr(39)||v_pivot(x)||chr(39)||||p_pivot_val||',null))as"'||v_pivot(x)||'",';LOOP;v_sqlrtrim(v_sql,',');IFp_whereISNOTTHENv_sqlv_sql||'from'||p_table||p_where||'groupby'||v_group_by;ELSEv_sqlv_sql||'from'||p_table||'groupby'||v_group_by;ENDIF;p_print_sql(v_sql);p_refcv_sql;EXCEPTIONOTHERSTHENp_refcSELECT'x'FROMdualWHERE1;END;END;/3.CREATETABLEt_col_strASSELECT*FROMt_col_row;||concatSELECTconcat('a','b')FROMdual;1).||ORconcat:8i,9i,10gSELECT*FROMt_col_str;SELECTID,c1||','||c2||','||c3ASc123FROMt_col_str;4.CREATETABLEt_row_str(IDINT,colVARCHAR2(10));INSERTINTOt_row_strVALUES(1,'a');INSERTINTOt_row_strVALUES(1,'b');INSERTINTOt_row_strVALUES(1,'c');INSERTINTOt_row_strVALUES(2,'a');INSERTINTOt_row_strVALUES(2,'d');INSERTINTOt_row_strVALUES(2,'e');INSERTINTOt_row_strVALUES(3,'c');COMMIT;SELECT*FROMt_row_str;1).decode:8i,9i,10g本SELECTid,MAX(decode(rn,1,col,NULL))||MAX(decode(rn,2,','||col,NULL))||MAX(decode(rn,3,','||col,NULL))strFROM(SELECTid,col,row_number()over(PARTITIONBYidORDERBYcol)rnFROMt_row_str)tGROUPidORDER1;2).row_numberlead:8i,9i,10g本SELECTid,strFROM(SELECTid,row_number()over(PARTITIONBYidORDERBYcol)ASrn,col||lead(','||col,1)over(PARTITIONBYidORDERBYcol)||lead(','||col,2)over(PARTITIONBYORDERBYcol)||lead(','||col,3)over(PARTITIONBYidcol)strFROMt_row_str)WHERE=ORDER1;3).MODEL:10g本SELECTid,substr(str,2)strFROMt_row_strMODELRETURNUPDATEDPARTITIONBY(ID)DIMENSIONBY(row_number()over(PARTITIONBYIDORDERcol)rn)MEASURES(CAST(colVARCHAR2(20))ASstr)RULESUPSERTITERATE(3)UNTIL(presentv(str[iteration_number+2],1,0)=0)(str[0]=str[0]||','||str[iteration_number+1])ORDERBY1;4).sys_connect_by_path:8i,9i,10g本SELECTt.idid,MAX(substr(sys_connect_by_path(t.col,','),2))strFROM(SELECTid,col,row_number()over(PARTITIONBYidORDERBYcol)rnFROMt_row_str)tSTARTWITHrn=1CONNECTBYrn=PRIOR+1PRIORidGROUPBYt.id;10gSELECTt.idid,substr(sys_connect_by_path(t.col,','),2)str(SELECTid,col,row_number()over(PARTITIONidORDERBYcol)FROMt_row_str)tWHEREconnect_by_isleaf=1STARTWITHrn=1CONNECTBYrn=PRIOR+1id=PRIORid;5).wmsys.wm_concat:10greplace。SELECTid,REPLACE(wmsys.wm_concat(col),',','/')FROMt_row_strGROUPBYid;5.換成列CREATETABLEt_str_colASSELECTID,c1||','||c2||','||c3ASc123FROMt_col_str;SELECT*FROMt_str_col;1).substr+instr:8i,9i,10gSELECTid,c123,substr(c123,1,instr(c123||',',',',1,1)-1)c1,substr(c123,instr(c123||',',',',1,1)1,instr(c123||',',',',1,2)-instr(c123||',',',',1,1)-1)c2,substr(c123,instr(c123||',',',',1,2)1,instr(c123||',',',',1,3)-instr(c123||',',',',1,2)-1)c3FROMt_str_colORDER1;2).regexp_substr:10gSELECTid,c123,rtrim(regexp_substr(c123||',','.*?'||',',1,1),',')ASc1,rtrim(regexp_substr(c123||',','.*?'||',',1,2),',')ASc2,rtrim(regexp_substr(c123||',','.*?'||',',1,3),',')ASc3FROMt_str_colORDER1;6.換成行CREATETABLEt_str_rowSELECTid,MAX(decode(rn,1,col,NULL))||MAX(decode(rn,2,','||col,NULL))||MAX(decode(rn,3,','||col,NULL))strFROM(SELECTid,col,row_number()over(PARTITIONBYidORDERBYcol)rnFROMt_row_str)tGROUPidORDER1;SELECT*FROMt_str_row;1).ALL:8i,9i,10gSELECTid,1ASp,substr(str,1,instr(str||',',',',1,1)-1)FROMt_str_rowUNIONALLSELECTid,2p,substr(str,instr(str||',',',',1,1)+1,instr(str||',',',',1,2)-instr(str||',',',',1,1)-cvFROMt_str_rowUNIONALLSELECTid,3p,substr(str,instr(str||',',',',1,1)+1,instr(str||',',',',1,2)-instr(str||',',',',1,1)-cvFROMt_str_rowORDERBY1,2;10gSELECTid,1ASp,rtrim(regexp_substr(str||',','.*?'||',',1,1),',')AScvFROMt_str_rowUNIONALLSELECTid,2ASp,rtrim(regexp_substr(str||',','.*?'||',',1,2),',')ASFROMt_str_rowUNIONALLSELECTid,3ASrtrim(regexp_substr(str||',','.*?'||',',1,3),',')cvFROMt_str_rowORDERBY1,2;2).VARRAY:8i,9i,10gCREATEORREPLACETYPEins_seq_typeVARRAY(8)NUMBER;SELECT*FROMTABLE(ins_seq_type(1,2,3,4,5));SELECTt.id,c.column_valueASp,substr(t.ca,instr(t.ca,',',1,c.column_value)+1,instr(t.ca,',',1,c.column_value1)-(instr(t.ca,',',1,c.column_value)+1))FROM(SELECTid,','||str||','ca,length(str||',')-nvl(length(REPLACE(str,',')),0)cntFROMt_str_row)tINNERJOINTABLE(ins_seq_type(1,2,3))cc.column_tORDERBY1,2;3).SEQUENCEseries:CONNECTBY,ROWNUM+all_objects,CUBE8i,9i,10g本SELECTt.id,c.lvsubstr(t.ca,instr(t.ca,',',1,c.lv)1,instr(t.ca,',',1,c.lv+1)-(instr(t.ca,',',1,c.lv)+1))AScvFROM(SELECTid,','||str||','ca,length(str||',')-nvl(length(REPLACE(str,',')),0)cntFROMt_str_row)t,(SELECTLEVELlvFROMdualCONNECTBYLEVEL<=5)cWHEREc.lv<=tORDERBY1,2;SELECTt.id,c.rnp,substr(t.ca,instr(t.ca,',',1,c.rn)+1,instr(t.ca,',',1,c.rn1)-(instr(t.ca,',',1,c.rn)+1))cvFROM(SELECTid,','||ASca,length(str|-nvl(length(REPLACE(str,',')),cntFROMt_str_row)t,(SELECTrownumFROMall_objectsWHERErownum5)WHEREc.rn<=tORDERBY1,2;SELECTt.id