oracle 行列倒置,Oracle 行列转换小结

-- 建立测试数据

drop table heyf_t8;

create table heyf_t8 (

APP_ID VARCHAR2(20) ,

APP_CATEGORY_ID VARCHAR2(20) );

insert into heyf_t8 values ('1','A');

insert into heyf_t8 values ('1','b');

insert into heyf_t8 values ('1','c');

insert into heyf_t8 values ('2','sdf');

insert into heyf_t8 values ('2','B');

insert into heyf_t8 values ('2','C');

insert into heyf_t8 values ('2','C');

insert into heyf_t8 values ('3','D');

insert into heyf_t8 values ('3','E');

commit;

-- 实现方法1 (9i,sys_connect_by_path + start with … connect by … prior + 分析函数)

select app_id,

ltrim(max(sys_connect_by_path(app_category_id, ',')), ',') categ_ids

from (select t.app_id,

t.app_category_id,

(row_number()

over(partition by app_id order by app_category_id)) numid

from heyf_t8 t)

start with numid = 1

connect by numid - 1 = prior numid

and app_id = prior app_id

group by app_id;-- 实现方法2 ( 10G,sys_connect_by_path + start with … connect by … prior + 分析函数)

select app_id,

ltrim(sys_connect_by_path(app_category_id, ','), ',') categ_ids

from (select t.app_id,

t.app_category_id,

(row_number()

over(partition by app_id order by app_category_id)) numid

from heyf_t8 t)

WHERE connect_by_isleaf = 1

start with numid = 1

connect by numid - 1 = prior numid

and app_id = prior app_id;

-- connect_by_isleaf 字段是10g中新提供的一个伪列，他可以用来判断该条记录是否是树形记录的叶节点

-- 实现方法3 ( 10G,wmsys.wm_concat)

--建立用户

@/opt/oracle/products/10.2.0/rdbms/admin/owmctab.plb

--建立类型

@$ORACLE_HOME/rdbms/admin/owmaggrb.plb

--建立wm_concat函数

@/opt/oracle/products/10.2.0/rdbms/admin/owmaggrs.plb

select app_id, wmsys.wm_concat(app_category_id) categ_ids

from heyf_t8 t

group by t.app_id;

-- 实现方法4 (建立自己的函数)

CREATE OR REPLACE FUNCTION rowtocol(p_slct IN VARCHAR2,

p_dlmtr IN VARCHAR2 DEFAULT ',')

RETURN VARCHAR2 AUTHID CURRENT_USER AS

/*1) Column should be character type.

2) If it is non-character type, column has to be converted into character type.

3) If the returned rows should in a specified order, put that ORDER BY CLASS in the SELECT statement argument.

4) If the SQL statement happened to return duplicate values, and if you don't want that to happen, put DISTINCT in the SELECT statement argument. */

TYPE c_refcur IS REF CURSOR;

lc_str VARCHAR2(4000);

lc_colval VARCHAR2(4000);

c_dummy c_refcur;

l number;

BEGIN

OPEN c_dummy FOR p_slct;

LOOP

FETCH c_dummy

INTO lc_colval;

EXIT WHEN c_dummy%NOTFOUND;

lc_str := lc_str || p_dlmtr || lc_colval;

END LOOP;

CLOSE c_dummy;

RETURN SUBSTR(lc_str, 2);

EXCEPTION

WHEN OTHERS THEN

lc_str := SQLERRM;

IF c_dummy%ISOPEN THEN

CLOSE c_dummy;

END IF;

RETURN lc_str;

END;

select a.department_id,a.department_name,a.location_id,

rowtocol('select distinct job_id from hr.employees where department_id='||a.department_id) as jobs

from hr.departments a

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

10 Administration 1700 AD_ASST

20 Marketing 1800 MK_MAN,MK_REP

30 Purchasing 1700 PU_CLERK,PU_MAN

40 Human Resources 2400 HR_REP

50 Shipping 1500 SH_CLERK,ST_CLERK,ST_MAN

60 IT 1400 IT_PROG

70 Public Relations 2700 PR_REP

80 Sales 2500 SA_MAN,SA_REP

90 Executive 1700 AD_PRES,AD_VP

100 Finance 1700 FI_ACCOUNT,FI_MGR

110 Accounting 1700 AC_ACCOUNT,AC_MGR

字符串 --&gt 多行

-- 实现方法1

define str ='''aa,bb,cc,dd,ee''';

select rn, substr(text, sbegin, send - sbegin) value

from (select a.text,

a.rn,

decode(a.rn - 1, 0, 0, instr(a.text, ',', 1, a.rn - 1)) + 1 sbegin,

decode(instr(a.text, ',', 1, a.rn),

0,

length(&str) + 1,

instr(a.text, ',', 1, a.rn)) send

from (select &str text, rownum rn

from dual

connect by rownum <= 1 + length(&str) -

length(replace(&str, ','))) a)

-- 实现方法2 (建立函数)

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

--- 字符串转换成数字列

CREATE OR REPLACE TYPE "NUMRECTYPE_ORDER" AS OBJECT(id NUMBER,num NUMBER);

CREATE OR REPLACE TYPE "NUMTABLETYPE_ORDER" AS TABLE OF numRecType_order;

CREATE OR REPLACE FUNCTION str2numList_order( p_string IN VARCHAR2 ) RETURN numTableType_order

AS

v_str VARCHAR2(5000) DEFAULT p_string || ',' ;

v_n NUMBER(6);

v_data numTableType_order := numTableType_order();

BEGIN

LOOP

v_n := INSTR( v_str, ',' );

EXIT WHEN (NVL(v_n,0) = 0);

v_data.extend;

v_data(v_data.COUNT) := numrectype_order(v_data.COUNT, TO_NUMBER(LTRIM(RTRIM(SUBSTR(v_str,1,v_n-1)))));

v_str := SUBSTR( v_str, v_n+1 );

END LOOP;

RETURN v_data;

END;

select * from

table(cast(str2numlist_order ('123,23,34,54') as numtabletype_order ))

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

--- 字符串转换成字符列

CREATE OR REPLACE TYPE "VARRECTYPE_ORDER" AS OBJECT(id NUMBER,var VARCHAR2(128));

CREATE OR REPLACE TYPE "VARTABLETYPE_ORDER" AS TABLE OF varRecType_order;

CREATE OR REPLACE FUNCTION str2varList_order( p_string IN VARCHAR2 ) RETURN VarTableType_order

AS

v_str VARCHAR2(5000) DEFAULT p_string || ',';

v_n NUMBER(6);

v_data VarTableType_order := VarTableType_order();

BEGIN

LOOP

v_n :=INSTR( v_str, ',' );

EXIT WHEN (NVL(v_n,0) = 0);

v_data.extend;

v_data( v_data.COUNT ) := varrectype_order(v_data.COUNT, LTRIM(RTRIM(SUBSTR(v_str,1,v_n-1))));

v_str := SUBSTR( v_str, v_n+1 );

END LOOP;

RETURN v_data;

END;

select * from

table(cast(str2varlist_order('aa,bb,cc,dd,ee') as vartabletype_order))