Oracle实现行列转换的方法分析
本文实例讲述了Oracle实现行列转换的方法。分享给大家供大家参考，具体如下：
1、固定列数的行列转换
如：
studentsubjectgrade --------------------------- student1语文80 student1数学70 student1英语60 student2语文90 student2数学80 student2英语100 ……
转换为：
语文数学英语 student1807060 student29080100 ……
语句如下：
selectstudent, sum(decode(subject,'语文',grade,null))"语文", sum(decode(subject,'数学',grade,null))"数学", sum(decode(subject,'英语',grade,null))"英语" fromtable groupbystudent;
2、不定列行列转换
如：
c1c2 -------------- 1我 1是 1谁 2知 2道 3不 ……
转换为
1我是谁 2知道 3不
这一类型的转换可以借助于PL/SQL来完成，这里给一个例子
CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER) RETURNVARCHAR2 IS Col_c2VARCHAR2(4000); BEGIN FORcurIN(SELECTc2FROMtWHEREc1=tmp_c1)LOOP Col_c2:=Col_c2||cur.c2; ENDLOOP; Col_c2:=rtrim(Col_c2,1); RETURNCol_c2; END; selectdistinctc1,get_c2(c1)cc2fromtable;
或者不用pl/sql，利用分析函数和CONNECT_BY实现：
SELECTc1,SUBSTR(MAX(SYS_CONNECT_BY_PATH(c2,';')),2)NAME FROM(SELECTc1,c2,rn,LEAD(rn)OVER(PARTITIONBYc1ORDERBYrn)rn1 FROM(SELECTc1,c2,ROW_NUMBER()OVER(ORDERBYc2)rn FROMt)) STARTWITHrn1ISNULL CONNECTBYrn1=PRIORrn GROUPBYc1;
3、列数不固定（交叉表行列转置）
这种是比较麻烦的一种，需要借助pl/sql：
原始数据：
CLASS1CALLDATECALLCOUNT 12005-08-0840 12005-08-076 22005-08-0877 32005-08-0933 32005-08-089 32005-08-0721
转置后：
CALLDATECallCount1CallCount2CallCount3 ------------------------------------------ 2005-08-090033 2005-08-0840779 2005-08-076021
试验如下：
1）.建立测试表和数据
CREATETABLEt(
class1VARCHAR2(2BYTE),
calldateDATE,
callcountINTEGER
);
INSERTINTOt(class1,calldate,callcount)
VALUES('1',TO_DATE('08/08/2005','MM/DD/YYYY'),40);
INSERTINTOt(class1,calldate,callcount)
VALUES('1',TO_DATE('08/07/2005','MM/DD/YYYY'),6);
INSERTINTOt(class1,calldate,callcount)
VALUES('2',TO_DATE('08/08/2005','MM/DD/YYYY'),77);
INSERTINTOt(class1,calldate,callcount)
VALUES('3',TO_DATE('08/09/2005','MM/DD/YYYY'),33);
INSERTINTOt(class1,calldate,callcount)
VALUES('3',TO_DATE('08/08/2005','MM/DD/YYYY'),9);
INSERTINTOt(class1,calldate,callcount)
VALUES('3',TO_DATE('08/07/2005','MM/DD/YYYY'),21);
COMMIT;
2）.建立refcursor准备输出结果集
CREATEORREPLACEPACKAGEpkg_getrecord IS TYPEmyrctypeISREFCURSOR; ENDpkg_getrecord;
3）.建立动态sql交叉表函数，输出结果集
CREATEORREPLACEFUNCTIONfn_rs RETURNpkg_getrecord.myrctype IS sVARCHAR2(4000); CURSORc1IS SELECT',sum(casewhenClass1=' ||class1 ||'thenCallCountelse0end)' ||'"CallCount' ||class1 ||'"'c2 FROMt GROUPBYclass1; r1c1%ROWTYPE; list_cursorpkg_getrecord.myrctype; BEGIN s:='selectCallDate'; OPENc1; LOOP FETCHc1INTOr1; EXITWHENc1%NOTFOUND; s:=s||r1.c2; ENDLOOP; CLOSEc1; s:=s||'fromTgroupbyCallDateorderbyCallDatedesc'; OPENlist_cursorFORs; RETURNlist_cursor; ENDfn_rs;
4）.测试在sqlplus下执行：
varresultsrefcursor; exec:results:=fn_rs; printresults; CALLDATECallCount1CallCount2CallCount3 --------------------------------------------- 2005-08-090033 2005-08-0840779 2005-08-076021
说明:decode
DECODE(value,if1,then1,if2,then2,if3,then3,...else)
Value代表某个表的任何类型的任意列或一个通过计算所得的任何结果。当每个value值被测试，如果value的值为if1，Decode函数的结果是then1；如果value等于if2，Decode函数结果是then2；等等。事实上，可以给出多个if/then配对。如果value结果不等于给出的任何配对时，Decode结果就返回else。
另外，还可以用decoder函数来比较大小，如下:
selectdecode(sign(变量1-变量2),-1,变量1,变量2)fromdual;--取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
更多关于Oracle相关内容感兴趣的读者可查看本站专题：《Oracle常用函数汇总》、《Oracle日期与时间操作技巧总结》及《php+Oracle数据库程序设计技巧总结》
希望本文所述对大家Oracle数据库程序设计有所帮助。