oracle中的行列转换函数,小弟我收集到的oracle中的“行列转换”函数

我收集到的oracle中的“行列转换”函数

1、第一种

first implementation simply uses static sql to select all of the values for B from

T for a given A and string them together:

函数代码：

CREATE OR REPLACE FUNCTION get_transposed (p_a IN VARCHAR2)

RETURN VARCHAR2

IS

l_str VARCHAR2 (2000) DEFAULT NULL;

l_sep VARCHAR2 (1) DEFAULT NULL;

BEGIN

FOR x IN (SELECT b

FROM t

WHERE a = p_a)

LOOP

l_str := l_str || l_sep || x.b;

l_sep := '-';

END LOOP;

RETURN l_str;

END;

/

样例：

column t format a30;

drop table t;

create table t

( a varchar2(25),

b varchar2(25)

);

insert into t values ( '210','5000' );

insert into t values ( '210','5001' );

insert into t values ( '210','5002' );

insert into t values ( '220','6001' );

insert into t values ( '220','6002' );

commit;

select a, get_transposed( a ) t

from t

group by a

/

A T

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

210 5000-5001-5002

220 6001-6002

2、第二种

next example is more complex.  We will pass in the name of the 'key' column (the

column to pivot on), a value for that column, the name of the column to actually select

out and string together and finally the table to select from:

函数代码：

create or replace

function transpose( p_key_name in varchar2,

p_key_val in varchar2,

p_other_col_name in varchar2,

p_tname in varchar2 )

return varchar2

as

type rc is ref cursor;

l_str varchar2(4000);

l_sep varchar2(1);

l_val varchar2(4000);

l_cur rc;

begin

open l_cur for 'select '||p_other_col_name||'

from '|| p_tname || '

where ' || p_key_name || ' = :x '

using p_key_val;

loop

fetch l_cur into l_val;

exit when l_cur%notfound;

l_str := l_str || l_sep || l_val;

l_sep := '-';

end loop;

close l_cur;

return l_str;

end;

/

使用样例：

REM List the values of "B" for a given value

REM of "A" in the table "T"

select a, transpose( 'a', a, 'b', 't' ) t

from t

group by a

/

A T

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

210 5000-5001-5002

220 6001-6002

3、第三种

直接使用wm_sys.wm_concat，它可以使用“,”来连接字符串

参考样例：http://blog.csdn.net/yy_mm_dd/article/details/3182953

SQL> create table idtable (id number,name varchar2(30));

Table created

SQL> insert into idtable values(10,'ab');

1 row inserted

SQL> insert into idtable values(10,'bc');

1 row inserted

SQL> insert into idtable values(10,'cd');

1 row inserted

SQL> insert into idtable values(20,'hi');

1 row inserted

SQL> insert into idtable values(20,'ij');

1 row inserted

SQL> insert into idtable values(20,'mn');

1 row inserted

SQL> select * from idtable;

ID NAME

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

10 ab

10 bc

10 cd

20 hi

20 ij

20 mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) name from idtable

2 group by id;

ID NAME

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

10 ab,bc,cd

20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable;

ID NAME

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

10 ab,bc,cd

10 ab,bc,cd

10 ab,bc,cd

20 ab,bc,cd,hi,ij,mn

20 ab,bc,cd,hi,ij,mn

20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable;

ID NAME

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

10 ab

10 ab,bc

10 ab,bc,cd

20 ab,bc,cd,hi

20 ab,bc,cd,hi,ij

20 ab,bc,cd,hi,ij,mn

6 rows selected

个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable;

ID NAME

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

10 ab,bc,cd

10 ab,bc,cd

10 ab,bc,cd

20 hi,ij,mn

20 hi,ij,mn

20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable;

ID NAME

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

10 ab

10 bc

10 cd

20 hi

20 ij

20 mn

6 rows selected

4、第四种

自定义一个方法，相当于是wm_concat的源码，能够修改满足个性化的要求。

代码：

CREATE OR REPLACE TYPE string_agg_type AS OBJECT

(

total VARCHAR2 (10000),

STATIC FUNCTION ODCIAggregateInitialize (

sctx IN OUT string_agg_type)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateIterate (

self IN OUT string_agg_type,

VALUE IN VARCHAR2)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateTerminate (

self IN string_agg_type,

returnValue OUT VARCHAR2,

flags IN NUMBER)

RETURN NUMBER,

MEMBER FUNCTION ODCIAggregateMerge (

self IN OUT string_agg_type,

ctx2 IN string_agg_type)

RETURN NUMBER);

CREATE OR REPLACE TYPE BODY string_agg_type

IS

STATIC FUNCTION ODCIAggregateInitialize (sctx IN OUT string_agg_type)

RETURN NUMBER

IS

BEGIN

sctx := string_agg_type (NULL);

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateIterate (self IN OUT string_agg_type,

VALUE IN VARCHAR2)

RETURN NUMBER

IS

BEGIN

self.total := self.total || ',' || VALUE;

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateTerminate (self IN string_agg_type,

returnValue OUT VARCHAR2,

flags IN NUMBER)

RETURN NUMBER

IS

BEGIN

returnValue := LTRIM (self.total, ',');

RETURN ODCIConst.Success;

END;

MEMBER FUNCTION ODCIAggregateMerge (self IN OUT string_agg_type,

ctx2 IN string_agg_type)

RETURN NUMBER

IS

BEGIN

self.total := self.total || ctx2.total;

RETURN ODCIConst.Success;

END;

END;

/

-- 最终要调用的方法

CREATE OR REPLACE FUNCTION stragg (input VARCHAR2)

RETURN VARCHAR2

PARALLEL_ENABLE

AGGREGATE USING string_agg_type;

/