Oracle行列互换

来源：互联网发布：q叔淘宝店铺网址编辑：程序博客网时间：2024/05/05 23:04

近几天一直在弄Oracle-SQL的问题，涉及到了一些平时没有用到的东西，也因此而在这里郁闷了好久。现在问题得到了解决虽说不算完美。但是还是和大家一起分享一下。

行列转换之一：sum（case when.. then.. else.. end） as 语句

这种也可能是我们遇到的第一个行列转换的方法。巧妙的利用汇总和判断语句就可以解决的。

先看一个简单的基础表：如下图

create table STUDENT

( www.2cto.com

STUNAME NVARCHAR2(50),

SUBJECTNAME NVARCHAR2(50),

SUBJECTSCORE NUMBER

)

表中有两个人的成绩，每一行代表每个学生该学科的成绩。这个也是我们初学SQL时候很常见的一个表结构了。

要求：把上面的多行值以学生为单位转化为2行值，可以很直观的看出每个学生的各科成绩。于是乎便有了下面的语句。

select

s.stuname,

sum(case when s.subjectname='语文' then s.subjectscore else 0 end ) as 语文成绩,

sum(case when s.subjectname='数学' then s.subjectscore else 0 end ) as 数学成绩,

sum(case when s.subjectname='英语' then s.subjectscore else 0 end ) as 英语成绩

from student s

group by s.stuname

order by s.stuname

执行后所得结果：

上面的做法也很容易让人理解，在这里就不多说了，但是上面的也是有局限性的。因为此处毕竟存在着一些固定值。比如case 中的“语文，数学。。”不过遇到小的业务需求，还是可以拿来直接用的。比如一个学生成绩管理系统这个就可以满足了。

行列转换之二：sys_connect_by_path系统函数，自定义函数，connect by sname = prior sname and rank-1 = prior rank递归语句 www.2cto.com

素材还是上面的表，下面我想得到的结果是：

把一个对象的各个学科的属性连接起来放入到一个列中，用字符串存放。

当时看到这个业务需求的时候，当然学生这个只是举个例子。很没有头绪。但是业务那边也急着要数据，没办法。只好硬着头皮想办法。首先就想起了这个要用连接字符串的函数，还需要判断循环之类的。于是乎就想起了这样做，创建一个自定义函数。来处理字符串不断的相加。

方法1：自定义函数，循环

CREATE OR REPLACE FUNCTION getallsubject(parameter varchar2)--有参数方法，字符串

RETURN varchar2

return_str varchar2(4000);--该方法返回一个字符串。

BEGIN

FOR rs IN

(

SELECT s.subjectname||':'||s.subjectscore as allshow

FROM student s

WHERE s.stuname=parameter--当参数一直符合条件 for循环插入结果集rs

) LOOP

return_str:=return_str||rs.allshow;--loop所有字符串，相加

END LOOP;

RETURN return_str;

END;

执行select s.stuname,getallsubject(s.stuname) from student s

可见，改方法对每一行值都进行了判断，产生了多条记录。然后select distinct s.stuname,getallsubject(s.stuname) from student s

效果：

得到了想要的结果。

方法2：利用oracle自带的sys_connect_by_path

要说明的是： www.2cto.com

所以在使用这个函数之前，我们必须先对源数据进行处理。第一步简单的处理一下。

create table stu1 as

select s.stuname sname,s.subjectname||s.subjectscore sshow from student s

select *from stu1

如下所示：

OK下面就可以写语句了。

select sname as 姓名,allstr 详细描述 from

(

select sname,allstr,

row_number() over(partition by sname order by sname,curr_level desc) ename_path_rank

from (

select sname,sshow,rank,level as curr_level,

ltrim(sys_connect_by_path(sshow,','),',') allstr from --把所有字符串相加

(

select s1.sname,s1.sshow,row_number() over(partition by s1.sname order by s1.sname,s1.sshow) rank www.2cto.com

from stu1 s1 order by s1.sname,s1.sshow--创建树关系，name可以作为parentid，rank可以作为childid

) connect by sname = prior sname and rank-1 = prior rank

)

where ename_path_rank=1;

执行结果：

也得到了我们想要的结果。当然不用学科之间的间隔符我们可以用replace函数自定义。

比较复杂一点的行列转换用以上两个方法都可以实现。但是这两个方法却都存在着一些优点和缺点。但是本人建议还是使用下面的方法比较靠谱

###################################################################################################################################

固定列数的行列转换

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

……
语句如下：

[sql] view plaincopy
select   student,sum(decode(subject,'语文',   grade,null))   "语文",     
sum(decode(subject,'数学',   grade,null))   "数学",     
sum(decode(subject,'英语',   grade,null))   "英语"     
from   table     
group   by   student     

不固定列数行列互换

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

转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成，这里给一个例子

[sql] view plaincopy
CREATE   OR   REPLACE   FUNCTION   get_c2(tmp_c1   NUMBER)       
RETURN   VARCHAR2       
IS       
Col_c2   VARCHAR2(4000);       
BEGIN     
FOR   cur   IN   (SELECT   c2   FROM   t   WHERE   c1=tmp_c1)   LOOP       
Col_c2   :=   Col_c2||cur.c2;       
END   LOOP;       
Col_c2   :=   rtrim(Col_c2,1);     
RETURN   Col_c2;       
END;  

[sql] view plaincopy
SQL>   select   distinct   c1   ,get_c2(c1)   cc2   from   table;  

看到这里，已经有了解决思路了，在oracle中建一个function，代码如下：

[sql] view plaincopy
CREATE OR REPLACE FUNCTION get_ver(tmp_boinstid VARCHAR2)  
RETURN   VARCHAR2  
IS  
vers   VARCHAR2(100);  
BEGIN  
FOR   cur   IN   (SELECT pln_ver FROM PMS_BUDGET_PLAN WHERE boinst_id=tmp_boinstid order by is_all_run)   LOOP  
vers   :=   vers||cur.pln_ver||',';  
END   LOOP;  
vers   :=   rtrim(vers,1);  
RETURN   vers;  
END get_ver;  

然后使用这个函数来查询就可以了，

[sql] view plaincopy
SELECT distinct p.pln_name,p.pln_year,get_ver(boinst_id) FROM PMS_BUDGET_PLAN p WHERE p.pln_type =20 and p.del_flag = -1  and boinst_id is not null  

需要注意的是，由于函数中也使用了sql，所以这个方法不太适合大数据量的查询，使用时应注意。

##################################################################################################################

oracle 动态交叉查询行列互换 oracle动态交叉表

使用视图动态创建视图

SQL code

CREATE OR REPLACE procedure DWDEV.dw_dis_proc(tabname in varchar2,--需要进行行转列操作的表名 group_col in varchar2,--查询结果要按某列或某些列分组的字段名

 column_col in varchar2,--要从行转成列的字段 value_col in varchar2,--需要聚合的值字段 Aggregate_func in varchar2 default 'max',--选用的聚合函数，可选，默认为max condition in varchar2 default '1=1',--条件语句，可选 colorder in varchar2 default null,--行转列后列的排序，可选 roworder in varchar2 default null,--行转列后记录的排序，可选 when_value_null in varchar2 default null,--若value_col字段的值聚合后为空，则转换成该值，可选 viewname in varchar2 default 'v_tmp'--创建的视图名称，可选，默认为v_tmp)                                 Authid Current_Userasc1 sys_refcursor;v1 varchar2(1000);sqlstr varchar2(10000);countTemp number(10); beginEXECUTE IMMEDIATE 'select count(distinct '||column_col||') from '||tabname into countTemp;   sqlstr :='create or replace view '||viewname||' as select '||group_col||',' ||Aggregate_func||'('||value_col||') as "summation",round('|| Aggregate_func||'('||value_col||')/'||countTemp||',2) as "average"';open c1 for 'select distinct '||column_col||' from '||tabname||' where '||condition|| case when colorder is not null then ' order by '||colorder end; loop fetch c1 into v1;exit when c1%notfound;sqlstr:=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';end loop;close c1;sqlstr:=sqlstr||' from '||tabname||' where '||condition||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;execute immediate sqlstr;end dw_dis_proc;

select * from v_tmp

################################################################################################################################

Oracle行列互换横表和纵表

tb_score表

转换成：

SQL实现代码：

[sql] view plaincopyprint?
create table tb_score  
(  
       stu_id int,  
       stu_name varchar2(20),  
       stu_object varchar2(10),  
       stu_score number(6,2)  
)  
go  
select * from tb_score;  
go  
insert into tb_score values(1,'andy','Chinese',80);  
insert into tb_score values(1,'andy','Math',70.05);  
insert into tb_score values(1,'andy','English',90);  
insert into tb_score values(2,'DK','Chinese',80.05);  
insert into tb_score values(2,'DK','Math',73.05);  
insert into tb_score values(2,'DK','English',99);  
go  
  
select stu_id,stu_name,sum(decode(stu_object,'Chinese',stu_score)) 语文,  
                         sum(decode(stu_object,'Math',stu_score)) 数学,  
                         sum(decode(stu_object,'English',stu_score)) 英语 from tb_score   
                         group by stu_id,stu_name;  
select stu_id,stu_name,sum(case stu_object when 'Chinese' then stu_score end) 语文,   
                         sum(case stu_object when 'Math' then stu_score end) 数学,   
                         sum(case stu_object when 'English' then stu_score end) 英语 from tb_score  
                         group by stu_id,stu_name;  
                                                  

上面两种方式都可以，