关于oracle 拆分字符串加列转行的问题

我有这样一张表VVV 类似

JPCZZH_ID	                JPCZZH_CH_NDJSNRJGM
JPCZZH0000000004	[{jpczzhJsYear:'2013',jpczzhChNdjsnrjgm:'22'},{jpczzhJsYear:'121',jpczzhChNdjsnrjgm:'22'}]
我想查询得到这样的结果

 
 
 
JPCZZH_ID
JPCZZHJSYEAR1
JPCZZHCHNDJSNRJGM1
JPCZZHJSYEAR2
JPCZZHCHNDJSNRJGM2
1
JPCZZH0000000004
'2013'
'22'
'121'
'22'

也就是说把JPCZZH_CH_NDJSNRJGM里的内容都拆成字段。冒号前面的是字段名(字段名要依次加123的)，后面是值，大括号括的有N组内容，就拆出来N乘以3个字段来。

下面是我的解决办法

先建一个视图

CREATE OR REPLACE VIEW V_SZH$NDJSNRJGM AS
SELECT RN,
       jpczzh_id,
       P1,
       SUBSTR(CV0, 0, INSTR(CV0, ':', 1, 1) - 1) || P1 A,
       SUBSTR(CV0, INSTR(CV0, ':', 1, 1) + 1) B
  FROM (SELECT T.RN,T.jpczzh_id,T.P1,
               SUBSTR(T.CA,
                      INSTR(T.CA, ',', 1, C.LV) + 1,
                      INSTR(T.CA, ',', 1, C.LV + 1) - (INSTR(T.CA, ',', 1, C.LV) + 1)) AS CV0
          FROM (SELECT RESULT1.RN RN,P1,jpczzh_id,',' || CV1 || ',' AS CA,
                       LENGTH(CV1 || ',') - NVL(LENGTH(REGEXP_REPLACE(CV1, ',', '')), 0) AS CNT
                  FROM (SELECT T1.RN,T1.jpczzh_id,C1.LV AS P1,
                               SUBSTR(T1.CA,
                                      INSTR(T1.CA, '},', 1, C1.LV) + 2,
                                      INSTR(T1.CA, '},', 1, C1.LV + 1) -
                                      (INSTR(T1.CA, '},', 1, C1.LV) + 2)) AS CV1
                          FROM (SELECT ROWNUM RN,jpczzh_id,
                                       '},' || REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm ,'\[{?|{|}\]','') || '},' AS CA,
                                       (LENGTH(REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm,'\[{?|{|}\]','') || '},') -
                                       NVL(LENGTH(REGEXP_REPLACE(jpczzh_ch_ndjsnrjgm,'\[{?|{|},|}\]','')),0)) / 2 AS CNT
                                  FROM jnjp_project_czzh) T1,
                               (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) C1
                         WHERE C1.LV <= T1.CNT) RESULT1) T,
               (SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= 10) C
         WHERE C.LV <= T.CNT
         ORDER BY jpczzh_id, P1, LV);

出来的结果是这样的：

   	RN	JPCZZH_ID	               P1	 A	                            B
1	1	JPCZZH0000000004	1	jpczzhJsYear1	            '2013'
2	1	JPCZZH0000000004	1	jpczzhChNdjsnrjgm1    '22'
3	1	JPCZZH0000000004	2	jpczzhJsYear2	           '121'
4	1	JPCZZH0000000004	2	jpczzhChNdjsnrjgm2	   '22'

然后再查询：

SELECT *  
  FROM VVV  t
  LEFT JOIN (SELECT jpczzh_id ID,
                    MAX(DECODE(A, 'jpczzhJsYear1', B)) JPCZZHJSYEAR1,
                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm1', B)) JPCZZHCHNDJSNRJGM1,
                    MAX(DECODE(A, 'jpczzhJsYear2', B)) JPCZZHJSYEAR2,
                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm2', B)) JPCZZHCHNDJSNRJGM2,
                    MAX(DECODE(A, 'jpczzhJsYear3', B)) JPCZZHJSYEAR3,
                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm3', B)) JPCZZHCHNDJSNRJGM3,
                    MAX(DECODE(A, 'jpczzhJsYear4', B)) JPCZZHJSYEAR4,
                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm4', B)) JPCZZHCHNDJSNRJGM4,
                    MAX(DECODE(A, 'jpczzhJsYear5', B)) JPCZZHJSYEAR5,
                    MAX(DECODE(A, 'jpczzhChNdjsnrjgm5', B)) JPCZZHCHNDJSNRJGM5
               FROM V_SZH$NDJSNRJGM
              GROUP BY jpczzh_id) ndjsnrjgm
    ON ndjsnrjgm.id = t.jpczzh_id
 

得出结果：

 
 
 
JPCZZH_ID
JPCZZHJSYEAR1
JPCZZHCHNDJSNRJGM1
JPCZZHJSYEAR2
JPCZZHCHNDJSNRJGM2
1
JPCZZH0000000004
'2013'
'22'
'121'
'22'

不知道还有没有好的解决办法，如果有的话请大家指教