oracle行列互换sql,ORACLE sql行列转置的实现方法

总结一下关于行列转置的实现方法1、固定列数的行列转换

如

student subject grade--------- ---------- --------student1

语文80

student1 数学70

student1 英语60

student2 语文90

student2 数学80

student2 英语100

……

转换为

student 语文 数学 英语

student1807060

student29080100

……

语句如下：

create table stu(

id number,

subject varchar2(30),

grade number);

insert into stu values(1, '语文',90);

insert into stu values(1, '英语',80);

insert into stu values(1, '数学',85);

insert into stu values(1, '体育',70);

insert into stu values(2, '语文',90);

insert into stu values(2, '英语',80);

insert into stu values(2, '数学',85);

insert into stu values(2, '体育',70);

select id,

sum(

decode(subject,'语文',grade, 0))语文,

sum(decode(subject,'英语', grade,0))英语,

sum(decode(subject,'数学', grade,0))数学,

sum(decode(subject,'体育', grade,0))体育

from stu

group by id;

2、不定列行列转换

如

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

……

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

这一类型的转换可以借助于PL/SQL来完成，这里给一个例子CREATEORREPLACEFUNCTIONget_c2(tmp_c1NUMBER)RETURNVARCHAR2IS

Col_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) NAME

FROM(SELECTc1, c2, rn, LEAD (rn)OVER(PARTITIONBYc1ORDERBYrn)

rn1

FROM(SELECTc1, c2, ROW_NUMBER ()OVER(ORDERBYc2) rn

FROMt))

STARTWITHrn1ISNULL

CONNECTBYrn1=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-07　6　　　　　021

试验如下：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_getrecordIS

TYPE myrctypeISREFCURSOR;ENDpkg_getrecord;/

3). 建立动态sql交叉表函数，输出结果集CREATEORREPLACEFUNCTIONfn_rs

RETURNpkg_getrecord.myrctypeIS

sVARCHAR2(4000);

CURSORc1IS

SELECT',sum(case when Class1='

||class1

||'then CallCount else 0 end)'

||'"CallCount'

||class1

||'"'c2

FROMt

GROUPBYclass1;

r1 c1%ROWTYPE;

list_cursor pkg_getrecord.myrctype;BEGIN

s :='select CallDate';

OPENc1;

LOOP

FETCHc1INTOr1;

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