Oracle行列转换总结.docx
- 文档编号:13904062
- 上传时间:2023-06-19
- 格式:DOCX
- 页数:18
- 大小:19.20KB
Oracle行列转换总结.docx
《Oracle行列转换总结.docx》由会员分享,可在线阅读,更多相关《Oracle行列转换总结.docx(18页珍藏版)》请在冰点文库上搜索。
Oracle行列转换总结
行列转换包括以下六种情况:
1.列转行
2.行转列
3.多列转换成字符串
4.多行转换成字符串
5.字符串转换成多列
6.字符串转换成多行
首先声明,有些例子需要如下10g及以后才有的知识:
A.掌握model子句,
B.正则表达式
C.加强的层次查询
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,c3cv FROMt_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等其他聚集函数替代。
sign()函数根据某个值是0、正数还是负数,分别返回0、1、-1
用sign和decode来完成比较字段大小来区某个字段
selectdecode(sign(字段1-字段2),-1,字段3,字段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.emp
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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 行列 转换 总结