oracle行列转换总结.docx
《oracle行列转换总结.docx》由会员分享，可在线阅读，更多相关《oracle行列转换总结.docx（27页珍藏版）》请在冰豆网上搜索。
oracle行列转换总结
oracle行列转换总结
最近论坛很多人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，
希望对大家有所帮助，同时有何错疏，恳请大家指出，
我也是在写作过程中学习，算是一起和大家学习吧。
行列转换包括以下六种情况：
*列转行
*行转列
*多列转换成字符串
*多行转换成字符串
*字符串转换成多列
*字符串转换成多行
下面分别进行举例介绍。
首先声明一点，有些例子需要如下10g及以后才有的知识：
a。
掌握model子句，
b。
正则表达式
c。
加强的层次查询
讨论的适用范围只包括8i,9i,10g及以后版本。
begin：
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,'c1'cn,c1cv
FROMt_col_row
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
UNIONALL
SELECTid,'c3'cn,c3cvFROMt_col_row;
若空行不需要转换，只需加一个where条件，
WHERECOLUMNISNOTNULL即可。
2）MODEL
适用范围：
10g及以后
SELECTid,cn,cvFROMt_col_row
MODEL
RETURNUPDATEDROWS
PARTITIONBY（ID）
DIMENSIONBY（0ASn）
MEASURES（'xx'AScn,'yyy'AScv,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,10g及以后版本
要创建一个对象和一个集合：
CREATETYPEcv_pairASOBJECT（cnVARCHAR2（10）,cvVARCHAR2（10））;
CREATETYPEcv_varrASVARRAY（8）OFcv_pair;
SELECTid,AScn,t.cvAScv
FROMt_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）））t
ORDERBY1,2;
2、行转列
CREATETABLEt_row_colAS
SELECTid,'c1'cn,c1cv
FROMt_col_row
UNIONALL
SELECTid,'c2'cn,c2cv
FROMt_col_row
UNIONALL
SELECTid,'c3'cn,c3cvFROMt_col_row;
SELECT*FROMt_row_colORDERBY1,2;
1）AGGREGATEFUNCTION
适用范围：
8i,9i,10g及以后版本
SELECTid,
MAX（decode（cn,'c1',cv,NULL））ASc1,
MAX（decode（cn,'c2',cv,NULL））ASc2,
MAX（decode（cn,'c3',cv,NULL））ASc3
FROMt_row_col
GROUPBYid
ORDERBY1;
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"
FROMemp
WHEREmgrIN（7566,7698）
ANDdeptnoIN（20,30）
GROUPBYmgr,deptno
ORDERBY1,2;
这里转置列为empno，固定列为mgr，deptno。
还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：
ID CN_1 CV_1 CN_2 CV_2 CN_3 CV_3
1 c1 v11 c2 v21 c3 v31
2 c1 v12 c2 v22 c3
3 c1 v13 c2 c3 v33
4 c1 c2 v24 c3 v34
5 c1 v15 c2 c3
6 c1 c2 c3 v35
7 c1 c2 c3
这种情况可以用分析函数实现：
SELECTid,
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_3
FROM（SELECTid,
cn,
cv,
row_number（）over（PARTITIONBYidORDERBYcn,cv）rn
FROMt_row_col）
GROUPBYID;
2）PL/SQL
适用范围：
8i,9i,10g及以后版本
这种对于行值不固定的情况可以使用。
下面是我写的一个包，包中
p_rows_column_real用于前述的第一种不限定列的转换；
p_rows_column用于前述的第二种不限定列的转换。
CREATEORREPLACEPACKAGEpkg_dynamic_rows_columnAS
TYPErefcISREFCURSOR;
PROCEDUREp_print_sql（p_txtVARCHAR2）;
FUNCTIONf_split_str（p_strVARCHAR2,p_divisionVARCHAR2,p_seqINT）
RETURNVARCHAR2;
PROCEDUREp_rows_column（p_table INVARCHAR2,
p_keep_cols INVARCHAR2,
p_pivot_colsINVARCHAR2,
p_where INVARCHAR2DEFAULTNULL,
p_refc INOUTrefc）;
PROCEDUREp_rows_column_real（p_table INVARCHAR2,
p_keep_colsINVARCHAR2,
p_pivot_colINVARCHAR2,
p_pivot_valINVARCHAR2,
p_where INVARCHAR2DEFAULTNULL,
p_refc INOUTrefc）;
END;
/
CREATEORREPLACEPACKAGEBODYpkg_dynamic_rows_columnAS
PROCEDUREp_print_sql（p_txtVARCHAR2）IS
v_lenINT;
BEGIN
v_len:
=length（p_txt）;
FORiIN1..v_len/250+1LOOP
dbms_output.put_line（substrb（p_txt,（i-1）*250+1,250））;
ENDLOOP;
END;
FUNCTIONf_split_str（p_strVARCHAR2,p_divisionVARCHAR2,p_seqINT）
RETURNVARCHAR2IS
v_firstINT;
v_last INT;
BEGIN
IFp_seq<1THEN
RETURNNULL;
ENDIF;
IFp_seq=1THEN
IFinstr（p_str,p_division,1,p_seq）=0THEN
RETURNp_str;
ELSE
RETURNsubstr（p_str,1,instr（p_str,p_division,1）-1）;
ENDIF;
ELSE
v_first:
=instr（p_str,p_division,1,p_seq-1）;
v_last :
=instr（p_str,p_division,1,p_seq）;
IF（v_last=0）THEN
IF（v_first>0）THEN
RETURNsubstr（p_str,v_first+1）;
ELSE
RETURNNULL;
ENDIF;
ELSE
RETURNsubstr（p_str,v_first+1,v_last-v_first-1）;
ENDIF;
ENDIF;
ENDf_split_str;
PROCEDUREp_rows_column（p_table INVARCHAR2,
p_keep_cols INVARCHAR2,
p_pivot_colsINVARCHAR2,
p_where INVARCHAR2DEFAULTNULL,
p_refc INOUTrefc）IS
v_sqlVARCHAR2（4000）;
TYPEv_keep_ind_byISTABLEOFVARCHAR2（4000）INDEXBYBINARY_INTEGER;
v_keepv_keep_ind_by;
TYPEv_pivot_ind_byISTABLEOFVARCHAR2（4000）INDEXBYBINARY_INTEGER;
v_pivotv_pivot_ind_by;
v_keep_cnt INT;
v_pivot_cnt INT;
v_max_cols INT;
v_partition VARCHAR2（4000）;
v_partition1VARCHAR2（4000）;
v_partition2VARCHAR2（4000）;
BEGIN
v_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;
FORiIN1..v_keep_cntLOOP
v_keep（i）:
=f_split_str（p_keep_cols,',',i）;
ENDLOOP;
FORjIN1..v_pivot_cntLOOP
v_pivot（j）:
=f_split_str（p_pivot_cols,',',j）;
ENDLOOP;
v_sql:
='selectmax（count（*））from'||p_table||'groupby';
FORiIN1..v_keep.LASTLOOP
v_sql:
=v_sql||v_keep（i）||',';
ENDLOOP;
v_sql:
=rtrim（v_sql,','）;
EXECUTEIMMEDIATEv_sql
INTOv_max_cols;
v_partition:
='select';
FORxIN1..v_keep.COUNTLOOP
v_partition1:
=v_partition1||v_keep（x）||',';
ENDLOOP;
FORyIN1..v_pivot.COUNTLOOP
v_partition2:
=v_partition2||v_pivot（y）||',';
ENDLOOP;
v_partition1:
=rtrim（v_partition1,','）;
v_partition2:
=rtrim（v_partition2,','）;
v_partition :
=v_partition||v_partition1||','||v_partition2||
',row_number（）over（partitionby'||v_partition1||
'orderby'||v_partition2||'）rnfrom'||p_table;
v_partition :
=rtrim（v_partition,','）;
v_sql :
='select';
FORiIN1..v_keep.COUNTLOOP
v_sql:
=v_sql||v_keep（i）||',';
ENDLOOP;
FORiIN1..v_max_colsLOOP
FORjIN1..v_pivot.COUNTLOOP
v_sql:
=v_sql||'max（decode（rn,'||i||','||v_pivot（j）||
',null））'||v_pivot（j）||'_'||i||',';
ENDLOOP;
ENDLOOP;
IFp_whereISNOTNULLTHEN
v_sql:
=rtrim（v_sql,','）||'from（'||v_partition||''||
p_where||'）groupby';
ELSE
v_sql:
=rtrim（v_sql,','）||'from（'||v_partition||
'）groupby';
ENDIF;
FORiIN1..v_keep.COUNTLOOP
v_sql:
=v_sql||v_keep（i）||',';
ENDLOOP;
v_sql:
=rtrim（v_sql,','）;
p_print_sql（v_sql）;
OPENp_refcFORv_sql;
EXCEPTION
WHENOTHERSTHEN
OPENp_refcFOR
SELECT'x'FROMdualWHERE0=1;
END;
PROCEDUREp_rows_column_real（p_table INVARCHAR2,
p_keep_colsINVARCHAR2,
p_pivot_colINVARCHAR2,
p_pivot_valINVARCHAR2,
p_where INVARCHAR2DEFAULTNULL,
p_refc INOUTrefc）IS
v_sqlVARCHAR2（4000）;
TYPEv_keep_ind_byISTABLEOFVARCHAR2（4000）INDEXBYBINARY_INTEGER;
v_keepv_keep_ind_by;
TYPEv_pivot_ind_byISTABLEOFVARCHAR2（4000）INDEXBYBINARY_INTEGER;
v_pivot v_pivot_ind_by;
v_keep_cntINT;
v_group_byVARCHAR2（2000）;
BEGIN
v_keep_cnt:
=length（p_keep_cols）-length（REPLACE（p_keep_cols,','）
升级会员 