java转置的方法_总结一下关于行列转置的实现方法（CSDN）

1、固定列数的行列转换

如

student subject grade--------- ---------- --------student1 语文80student1 数学70student1 英语60student2 语文90student2 数学80student2 英语100……

转换为

语文 数学 英语

student1807060student29080100……

语句如下：selectstudent,sum(decode(subject,'语文', grade,null)) "语文",sum(decode(subject,'数学', grade,null)) "数学",sum(decode(subject,'英语', grade,null)) "英语"fromtablegroupbystudent;2、不定列行列转换

如

c1 c2--- -----------1我1是1谁2知2道3不

……

转换为1我是谁2知道3不

这一类型的转换可以借助于PL/SQL来完成，这里给一个例子CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER)RETURNVARCHAR2ISCol_c2VARCHAR2(4000);BEGINFORcurIN(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) NAMEFROM(SELECTc1, c2, rn, LEAD (rn)OVER(PARTITIONBYc1ORDERBYrn) rn1FROM(SELECTc1, c2, ROW_NUMBER ()OVER(ORDERBYc2) rnFROMt))

STARTWITHrn1ISNULLCONNECTBYrn1=PRIOR rnGROUPBYc1;3、列数不固定(交叉表行列转置)

这种是比较麻烦的一种，需要借助pl/sql：

原始数据：

CLASS1     CALLDATE         CALLCOUNT12005-08-084012005-08-07622005-08-087732005-08-093332005-08-08932005-08-0721转置后：

CALLDATE     CallCount1 CallCount2 CallCount3------------ ---------- ---------- ----------2005-08-0900332005-08-08407792005-08-076021试验如下：1). 建立测试表和数据CREATETABLEt(

class1VARCHAR2(2BYTE),

calldate DATE,

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). 建立ref cursor准备输出结果集CREATEORREPLACEPACKAGE pkg_getrecordISTYPE myrctypeISREFCURSOR;ENDpkg_getrecord;/3). 建立动态sql交叉表函数，输出结果集CREATEORREPLACEFUNCTIONfn_rsRETURNpkg_getrecord.myrctypeISsVARCHAR2(4000);CURSORc1ISSELECT',sum(case when Class1='||class1||'then CallCount else 0 end)'||'"CallCount'||class1||'"'c2FROMtGROUPBYclass1;

r1 c1%ROWTYPE;

list_cursor pkg_getrecord.myrctype;BEGINs :='select CallDate';OPENc1;

LOOPFETCHc1INTOr1;EXITWHENc1%NOTFOUND;

s :=s||r1.c2;ENDLOOP;CLOSEc1;

s :=s||'from T group by CallDate order by CallDate desc';OPENlist_cursorFORs;RETURNlist_cursor;ENDfn_rs;/4). 测试在sql plus下执行：varresults refcursor;exec:results :=fn_rs;printresults;

CALLDATE        CallCount1 CallCount2 CallCount3--------------- ---------- ---------- ----------2005-08-0900332005-08-08407792005-08-076021

posted on 2007-12-03 17:06 都市淘沙者 阅读(631) 评论(0)  编辑  收藏 所属分类: Oracle/Mysql/Postgres/