oracle自定义函数实现行列转换

oracle技术群里一哥们提出个问题，说是不利用oracle自带的wmsys.wm_concat()函数，实现行列转换，这个问题蛮有意思的，就简单的写了个函数实现了下，简单的演示一下：

 

SQL> create table aaa (a int ,b int);

Table created.

SQL> insert into aaa values(1,2);

1 row created.

SQL> insert into aaa values(1,3);

1 row created.

SQL> insert into aaa values(2,2);

1 row created.

SQL> insert into aaa values(3,2);

1 row created.

SQL> insert into aaa values(4,2);

1 row created.

SQL> insert into aaa values(4,2);
1 row created.

SQL> insert into aaa values(5,2);

1 row created.

SQL> create or replace function my_concat(value int) return varchar2 as        #定义自定义函数
result varchar2(4000);
begin
        for cursor in (select b from aaa where a=value) loop
                result := result || cursor.b || ',';
        end loop;
        result := rtrim(result,',');
        return result;
end;
/ 

Function created.

 

#查询

SQL> select a,my_concat(a) from aaa group by a;

         A MY_CONCAT(A)
---------- ------------------------------
         1 2,3
         2 2
         4 2,2
         5 2
         3 2

看结果，实现了功能，当然还有很多方法可以实现，呵呵

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26463985/viewspace-712376/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26463985/viewspace-712376/