wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

 

2010-09-20

wmsys.wm_concat、sys_connect_by_path、自定义函数实现行列转换

 

最后一个函数自己所写，文章参考出处：http://junmail.javaeye.com/blog/250382

 

--构建测试表：

--Sql脚本:

create table test1

(

  ID   NUMBER ,

  NAME VARCHAR2(10)

);

 

create table test2

(

  ID   NUMBER ,

  ROLE VARCHAR2(10)

);

 

--测试数据

insert into test1 (ID, NAME) values (1, '张三');

insert into test1 (ID, NAME) values (2, '李四');

commit;

 

insert into test2 (ID, ROLE) values (1, '查询');

insert into test2 (ID, ROLE) values (1, '分析');

insert into test2 (ID, ROLE) values (1, '决策');

insert into test2 (ID, ROLE) values (2, '查询');

commit;

 

 

--要求输出结果：

      ID  NAME    ROLE

      --------------------

      1   张三       查询,分析,决策

      2   李四       查询

 

 

 

--方法一、使用wmsys.wm_concat

--            它是oracle在10g及其以上版本提供的一个内置函数,可以实现两字串的连接,默认是以逗号(',')进行连接

 

--Sql代码:

select test1.*,wmsys.wm_concat(role) role from test1,test2

where test1.id=test2.id group by test1.id,test1.NAME ;

 

 

--方法二、使用sys_connect_by_path

--            此函数也是实现字串的连接,在9i及以前就提供此函数,一般和connect by 语句结合来构造树状结构的查询

 

--Sql代码:

select id, name, max(ltrim(sys_connect_by_path(role, ','), ','))role

from

  (select

        row_number() over(partition by test1.id order by name) rn,

        test1.*, role

   from test1, test2  where test1.id =test2.id)

start with rn = 1

connect by prior rn = rn-1 and prior id = id

group by id, name

order by id ;

 

 

 

--方法三、使用自定义函数

 

--Sql代码

--创建function

create or replace function my_concat(p_id in number)

return varchar2

is

  result    varchar2(4000);

begin

       for rs in (select role from test2 where id=p_id) loop

           result :=result || rs.role || ',';

       end loop;

       result := rtrim(result,',');  --去掉最后一个空格

       return result;

end;

 

--查询sql

select test1.*,my_concat(test1.id)role from test1,test2 where test1.id=test2.id

group by test1.id,test1.name

order by test1.id ;

 

 

--拓展此函数

create or replace function my_concat2(table_name in  varchar2,   --表名

                                      p_col1     in  varchar2,   --查询条件的栏位名称

                                      p_value    in  varchar2,   --查询条件的栏位值

                                      p_col2     in  varchar2    --查询栏位，即连接栏位

)

return varchar2

IS

  type my_type is record(str varchar2(100));

  type cur is ref cursor ;

  my_cur    cur;

  rs        my_type;

  result    varchar2(4000);  --返回字符串连接后的结果集

  sql_str   varchar2(4000);  --动态查询sql语句

begin

       sql_str:='select '||p_col2||' from '||table_name||' where '||p_col1||'='||p_value;

       open my_cur for sql_str;

       loop

          fetch my_cur into rs;

                exit when my_cur%notfound;

                result :=result || rs.str || ',';

       end loop;

       close my_cur ;--关闭游标

       result := rtrim(result,',');  --去掉最后一个空格

       return result;

end;

 

 

SELECT id,NAME, my_concat2('test2','id',id,'role')role  FROM test1;