一分多行列转换

行列转换"/>

一分多行列转换

问题描述：

将以下表方式存放的数据：

转换成如下方式存放：

1、 建表语句及插入测试数据

--建表语句
create table INFC_C_GUARANTOR_NISJ
(CUST_NO      VARCHAR2(30),BAIL_ACCT_NO VARCHAR2(2000)
);
create table INFC_C_GUARANTOR_NISJ_NEW
(CUST_NO      VARCHAR2(30),BAIL_ACCT_NO VARCHAR2(2000)
);--测试数据
insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000290693', ',011101251020006862,');
insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000692449', ',9011101012510200105,011201251020000015,041101251020000193,');
insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS6000320019', ',011101251010001452,9011101012510200100,9011301012510200100,');
insert into Infc_c_Guarantor_Nisj (CUST_NO, BAIL_ACCT_NO) values ('CMS8000695502', ',9011001012510200009,9011101012510200102,011101251020005485,');
commit;

2、 实现转换的存储过程

CREATE OR REPLACE PROCEDURE SP_NISJ
IS
V_DOUHAO_NUM NUMBER;
V_CUST_NO    VARCHAR2(30);
CURSOR CUR_CUST_NO ISSELECT CUST_NOFROM INFC_C_GUARANTOR_NISJ;BEGINOPEN CUR_CUST_NO;EXECUTE IMMEDIATE 'TRUNCATE TABLE INFC_C_GUARANTOR_NISJ_NEW';LOOPFETCH CUR_CUST_NOINTO V_CUST_NO;EXIT WHEN CUR_CUST_NO%NOTFOUND;
SELECT LENGTHB(BAIL_ACCT_NO)-LENGTHB(REPLACE(BAIL_ACCT_NO,',','')) INTO V_DOUHAO_NUM
FROM INFC_C_GUARANTOR_NISJ
WHERE CUST_NO=V_CUST_NO;
FOR I IN 1..V_DOUHAO_NUM-1 LOOP
IF V_DOUHAO_NUM > 0 THENINSERT INTO INFC_C_GUARANTOR_NISJ_NEWSELECT CUST_NO,SUBSTR(BAIL_ACCT_NO,INSTR(BAIL_ACCT_NO, ',', 1,I) + 1,((INSTR(BAIL_ACCT_NO, ',', 1,I+1))-(INSTR(BAIL_ACCT_NO, ',', 1,I))-1) )FROM INFC_C_GUARANTOR_NISJWHERE CUST_NO=V_CUST_NO;
END IF;
END LOOP;
END LOOP;CLOSE CUR_CUST_NO;
COMMIT;
END;
/

3、 说明

写代码过程中，用到的中间存储过程：

CREATE OR REPLACE PROCEDURE ONE_TO_DUO
IS
V_DOUHAO_NUM NUMBER;
V_CUST_NO VARCHAR2(30) := 'CMS6000340435';
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE INFC_C_GUARANTOR_NISJ_NEW';
SELECT LENGTHB(BAIL_ACCT_NO)-LENGTHB(REPLACE(BAIL_ACCT_NO,',','')) INTO V_DOUHAO_NUM  
FROM INFC_C_GUARANTOR_NISJ 
WHERE CUST_NO=V_CUST_NO;
FOR I IN 1..V_DOUHAO_NUM-1 LOOP
IF V_DOUHAO_NUM > 0 THENINSERT INTO INFC_C_GUARANTOR_NISJ_NEWSELECT CUST_NO,SUBSTR(BAIL_ACCT_NO,INSTR(BAIL_ACCT_NO, ',', 1,I) + 1,((INSTR(BAIL_ACCT_NO, ',', 1,I+1))-(INSTR(BAIL_ACCT_NO, ',', 1,I))-1) )FROM INFC_C_GUARANTOR_NISJ WHERE CUST_NO=V_CUST_NO;
END IF;
END LOOP;
COMMIT;
END;
/