我收集到的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 VARCHAR2IS 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 ) tfrom tgroup by a/A T------------------------- ------------------------------210 5000-5001-5002220 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 replacefunction transpose( p_key_name in varchar2, p_key_val in varchar2, p_other_col_name in varchar2, p_tname in varchar2 )return varchar2as 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 valueREM of "A" in the table "T"select a, transpose( 'a', a, 'b', 't' ) t from t group by a/A T------------------------- ------------------------------210 5000-5001-5002220 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 createdSQL> insert into idtable values(10,'ab');1 row insertedSQL> insert into idtable values(10,'bc');1 row insertedSQL> insert into idtable values(10,'cd');1 row insertedSQL> insert into idtable values(20,'hi');1 row insertedSQL> insert into idtable values(20,'ij');1 row insertedSQL> insert into idtable values(20,'mn');1 row insertedSQL> select * from idtable; ID NAME---------- ------------------------------ 10 ab 10 bc 10 cd 20 hi 20 ij 20 mn6 rows selectedSQL> select id,wmsys.wm_concat(name) name from idtable 2 group by id; ID NAME---------- -------------------------------------------- 10 ab,bc,cd 20 hi,ij,mnSQL> 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,mn6 rows selectedSQL> 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,mn6 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,mn6 rows selectedSQL> 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 mn6 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_typeIS 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;/
?
?
